Gregg Stark on SQL Server

Sql Server Cursor Template

This is a template that I have been using in SQL

server for when I do have to write cursors.  Notice that there is only one fetch statement in there which is much slicker than the typical method of initially doing a fetch, with a while condition of while (@@fetch_status <> 0) and then another fetch in the while loop.  This obviously seemed tedious and too much code for me as I could never remember to change the fetch statement in both parts.  So I started using the template below...notice the while (1=1) and then the fetch inside with a break if it is the end of the cursor.  When I first saw this I couldn't believe it took me so long to come up with something like this.  Let me know if you have any tweaks or suggestions.

declare @myvariable varchar(50)

declare mycursor cursor forward_only 
    for Select field1 from mytable
open mycursor

while (1=1)
begin
  fetch next from mycursor into @myvariable
  if @@fetch_status <> 0
    break;

end
close mycursor
deallocate mycursor