Cursor Solution
The simplest and at the same time almost the most efficient solution involves the use of a cursor:
create procedure dbo.Sales_sel_by_StoreID_ProductID
@StoreID int,
@ProductID int
as begin
set noCount on
declare @report table(
transactionID int primary key clustered,
transactionTime dateTime not null,
amount money not null,
total money not null
)
declare runningTotalsCursor cursor for
select transactionID, TransactionTime, Amount
from dbo.Sales
where
StoreID = @StoreID and
ProductID = @ProductID and
order by
transactionID
declare @transactionID int
declare @transactionTime dateTime
declare @amount money
declare @total money
set @total = 0
open RunningTotalsCursor
while (0=0) begin
fetch next from RunningTotalsCursor into @transactionID, transactionTime, @amount
if @@fetch_status <> 0 break
set @total = @total + @amount
insert into @report(transactionID, transactionTime, amount, total) values(@transactionID, @transactionTime, @amount, @total)
end — while
close runningTotalsCursor
deallocate runningTotalsCursor
select transactionID, transactionTime, amount, total from @report order by transactionID
set noCount off
end
If you use SQL Profiler to test the efficiency of the solution, pay attention to the duration, reads, and CPU counter.
Correlated Query Solution
The simplest and the least efficient solution using a data set approach is one that takes advantage of a correlated query. This is almost the definition of a running total: Return all transaction IDs, transaction times, and amounts for a specific product, and store alongside it the sum of all amounts from the first transaction to the current one:
create procedure dbo.Sales_sel_by_StoreID_ProductID
@StoreID int,
@ProductID int
as begin
set noCount on
select top 10
a.transactionID,
a.transactionTime,
a.amount,
(select sum(amount)
from
dbo.Sales b
where
b.transactionID <= a.TransactionID and
b.StoreID = @StoreID and
b.ProductID = @ProductID
) as total
from
dbo.Sales a
where
a.StoreID = @StoreID and
a.ProductID = @ProductID
order by transactionID
set noCount off
end
This query will have N*(N+1)/2 row-reads from the sales table where N is the number of rows returned by the procedure. The consequence is terrible performance compared to the cursor solution. There is a similar solution using join and group by, that also needs N*N magnitude of reads.