Cursors

Posted by Joe Enos on August 11, 2021 · 2 mins read

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;