2010-08-03

Small cursor usage trick

Okay... everyone knows that cursors are bad. And yet we all use them from time to time. Historically I've written them like this:

declare @table sysname 
declare curs cursor for
select name from sys.tables
fetch next from curs into @table

while (@@fetch_stats = 0)
begin
  --do stuff
  fetch next from curs into @table
end

close curs
deallocate curs


While writing some code today, I had this insight:

declare @table sysname 
declare curs cursor for
select name from sys.tables

open curs

while (1=1)
begin
  fetch next from curs into @table
  if (@@fetch_status <> 0)
    break
  --do stuff
end
close curs
deallocate curs

With the advantage that I only have to maintain one fetch line as I'm inevitably adding and removing columns from the cursor. I'm sure that I'm not the first to come up with this, but hey... I thought it was nice.

No comments:

Post a Comment