Cursors in SQL Server

Here’s my preferred syntax for a simple one-way readonly cursor:

It uses the @variable syntax just because I find it weird in SQL Server to use a variable without the @ symbol. I don’t know if there are any meaningful differences in doing it this way, but this just feels more natural to me.

Many people will fetch upfront, then do a while @@fetch_status <> 0, and then fetch again at the end of the loop body. This example avoids that by fetching inside an infinite loop, and breaking when it stops fetching records.

declare @cur cursor;
set @cur = cursor local fast_forward for
    select * from
        select 1 [a]
        union select 2
        union select 3
    ) x
    where x.a > 0;
declare @a int;
open @cur;
while 1 = 1
    fetch next from @cur into @a;
    if @@fetch_status <> 0 break;
    print @a;
    -- do stuff
close @cur;
deallocate @cur;

Leave a Reply

Your email address will not be published.