How to return single rowset from SQL Server cursor?

Question

I have a query below that works in SQL Server to select the difference between a value in one column and the value from another column, but two rows earlier in the source table. The source table has three columns (PK bigint, Pv float, Cash float).

When I use this query, what I get back is a series of single-row rowsets, rather than one big rowset. I can insert these one-by-one into a temp table then select out of that, but this means for large tables I end up with a big temp table as well as a long delay to the first record being returned.

Is there any way to spool the results of this query efficiently as a single row set?

declare @PK bigint
declare @Pv float
declare @Cash float
declare @Cash_minus_1 float
declare @Cash_minus_2 float
set @Cash_minus_1 = 0
set @Cash_minus_2 = 0
declare myCursor cursor fast_forward
for select PK, Pv, Cash from MyTable order by PK
for read only
open myCursor
fetch next from myCursor
into @PK, @Pv, @Cash
while @@FETCH_STATUS = 0
begin
 select @PK, @Pv, @Cash, @Cash_minus_2
 set @Cash_minus_2 = @Cash_minus_1
 set @Cash_minus_1 = @Cash
 fetch next from myCursor
 into @PK, @Pv, @Cash
end
close myCursor
deallocate myCursor

"two rows earlier" - since you have no `ORDER BY` clause for your `SELECT`, this may as well be specified as "from some other random row in the table". If you can tell us which column(s) *should* define the order, such that "two rows earlier" actually makes sense, we should be able to help — Damien_The_Unbeliever, Jul 05 '13 at 07:40
@Damien_The_Unbeliever 2008 R2. Yes, appreciate the row order is not defined in the query, was trying to simplify the example as much as possible - have edited the example query. — Jon G, Jul 05 '13 at 08:08

score 2 · Accepted Answer · answered Jul 05 '13 at 07:43

2

Assuming SQL Server 2005 or later, you can use ROW_NUMBER() and a common table expression to arrange the table appropriately:

;With OrderedRows as (
    select PK, Pv, Cash,
        ROW_NUMBER() OVER (ORDER BY PK) as rn --Is this correct?
    from MyTable
)
select or1.PK,or1.Pv,or1.Cash,COALESCE(or2.Cash,0)
from
    OrderedRows or1
        left join
    OrderedRows or2
        or1.rn = or2.rn + 2

In the above, I've assumed PK defines the order in which rows should be considered, such that "two rows earlier" from your question even makes sense.

In general, Cursors should be viewed as a tool of last resort. You should always try, first, to formulate your query be talking about what the entire result set should look like - not how you (if you had to) would work it out, row by row.

answered Jul 05 '13 at 07:43

Damien_The_Unbeliever

234,701
27
340
448

Appreciate the caveats around cursor usage, but let's assume I have a good reason why I need to process the result set row-by-row (the real query behind this question is a lot more awkward and not suited to the self-join approach). I guess my question is specifically as to whether I can get the results of this query back as a single rowset, rather than whether I could re-write that simple example query in a more efficient way. – Jon G Jul 05 '13 at 08:18
@JonG - no, there's no way (other than your already sketched temp table/table variable staging area) to produce multiple rows that are part of the *same* result set from multiple SQL statements – Damien_The_Unbeliever Jul 05 '13 at 08:20

Nenad Zivkovic · Answer 2 · 2013-07-05T07:48:33.033

1

Use ROW_NUMBER function to assign numbers to each row in wanted order (needed because PK can have gaps which would break the query) and join the table on itself with -2 rows.

WITH CTE AS 
(
    SELECT PK, Pv, Cash, ROW_NUMBER() OVER (ORDER BY PK) RN from MyTable
)
SELECT C.PK, C.Pv, C.Cash, C2.Cash AS Cash_Minus_2 
FROM CTE C
LEFT JOIN CTE C2 ON C.RN = C2.RN + 2

In SQL Server 2012 there is even a window function LAG you can use directly.

SELECT PK, Pv, Cash, LAG(Cash,2,NULL) OVER (ORDER BY PK) AS Cash_Minus_2
FROM MyTable

edited Jul 05 '13 at 07:48

answered Jul 05 '13 at 07:42

Nenad Zivkovic

18,221
6
42
55

Thanks for the response, I appreciate the alternative forms this query could be re-written with, but I'm specifically asking here about ways to return result rows from a cursor query. – Jon G Jul 05 '13 at 08:21

How to return single rowset from SQL Server cursor?

2 Answers2