Tuesday, 17 June 2008

SQL Server 2005 - Generic Cursor Code

I always forget how to put a cursor together, so I'm blogging it!
 

DECLARE GenericCursor INSENSITIVE CURSOR FOR

       SELECT <<fields>>

       FROM <<table>>

       WHERE <<criteria>>

OPEN GenericCursor

DECLARE <<variables>>

FETCH NEXT FROM GenericCursor INTO <<variables>>

WHILE @@FETCH_STATUS = 0

BEGIN

       <<exec CRUD statement>>

       FETCH NEXT FROM GenericCursor INTO <<variables>>

END

CLOSE GenericCursor

DEALLOCATE GenericCursor

 
There are a few basic rules surrounding the field selection exactly matching the list of variables that you use. These things tend to work themselves out if error messages are correctly interpreted anyway, so I won't give a practical example ;-)
 
All fairly self-explanatory - and I suppose this code does not change betwen versions of SQL - but I have not tested that ...

No comments:

Post a Comment