Cursors

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
begin
    fetch next from @cur into @a;
    if @@fetch_status <> 0 break;
 
    print @a;
    -- do stuff
end;
close @cur;
deallocate @cur;