James,

I have a few comments about the Stored Proc you've posted here.

First, is this SQL Server 2000? If so, unless those same temp tables are being used elsewhere in another Stored Proc, you should really be using table variables instead. They are more efficient.

Secondly, again unless those temp tables are being used elsewhere and they need to be ordered elsewhere, there's no reason to order them. That's just wasting a lot of time.

As to whether or not views would be better or not, I'll leave that question to Sergey or one of the other experts here, as I don't think I've ever used SQL Server views.

~~Bonnie



>I am not criticizing the people here, they certainly know more
>about SQL Server than I do. All of the code they write is similar
>to the code below. I am supposed to modified this. I was hired as
>a FoxPro programmer, there is no more FoxPro to do so they have me
>doing SQL Server, which I don't mind.
>They use temporay tables in most of their programs. Would views be better?
>I am trying to learn how to index views, and cons to this?
>They warehouse for a lot of big companies. The servers are used to the max.
>
>
>
>PROCEDURE dbo.pr_Report_Ops_TotalPicksPerSKU
>
>@fulfillmentid int,
>@whseid varchar(3),
>@StartDate as datetime,
>@EndDate as datetime
>
>AS
>
>-- Data Source for the Total Picks Per SKU Report
>
>DECLARE @ShortName varchar(30)
>
>SET @StartDate= CONVERT(datetime,CONVERT(varchar,@StartDate,101) + ' 12:00:00 AM')
>SET @EndDate = CONVERT(datetime,CONVERT(varchar,@EndDate,101) + ' 11:59:59 PM')
>
>SET ROWCOUNT 0
>
>SET @ShortName = (SELECT short_name FROM fulfillment WHERE fulfillment_id = @fulfillmentid)
>
>SELECT
> convert( varchar,endtime,101) as pickdate, sku, qty as quantity, fromloc
>
>INTO
> #Tempfile
>FROM
> wms_taskdetail with (nolock)
>WHERE
> whseid = @whseid
> AND storerkey = @ShortName
> AND endtime between @StartDate and @EndDate
> and tasktype = 'PK'
>ORDER BY
> convert( varchar,endtime,101), sku
>
>SELECT
> pickdate, sku, fromloc, count(sku) as picks, sum(quantity) as quantity
>INTO
> #tempfile2
>FROM
> #tempfile
>GROUP BY
> pickdate, sku, fromloc
>ORDER BY
> pickdate, sku
>
>SELECT
> t.sku, t.fromloc, sum(t.picks) as picks, sum(t.quantity) as quantity, s.skugroup
>FROM
> #tempfile2 t, wms_sku s with (nolock)
>WHERE
> t.sku = s.sku
>GROUP BY
> t.sku, t.fromloc, s.skugroup
>ORDER BY
> t.picks desc, t.sku asc