Handling Cursor-Friendly Problems in T-SQL: Running Totals Example

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.