SQL TSQL Cursor Syntax – A quick reference

I know cursors are big heavy horrid things that make DBA’s shudder and T-SQL gurus cringe. Cursors aren’t usually required, instead some complex TSQL can do the same thing without having to loop through records. But the truth is people use them and so long as some thought is applied to the resource meter things shouldn’t be too bad.

 For the purists and wannabe’s I found this link useful: http://www.sql-server-performance.com/dp_no_cursors.asp

For the rest of us here is a SQL 2000 cursor. If you’re using SQL 2005 have a look here http://msdn2.microsoft.com/en-us/library/ms190028.aspx

DECLARE @price money
DECLARE @get_price CURSOR

SET @get_price = CURSOR FOR
     SELECT price FROM titles

OPEN @get_price
 
FETCH NEXT FROM @get_price INTO @price

WHILE (@@FETCH_STATUS = 0) 
BEGIN
     IF @Price < 20
 SELECT ‘Under 20′
     ELSE
 SELECT @Price

     FETCH NEXT FROM @get_price INTO @price
END

CLOSE @get_price
DEALLOCATE @get_price