SQL SERVER – Simple Example of Cursor

UPDATE: For working example using AdventureWorks visit : SQL SERVER – Simple Example of Cursor – Sample Cursor Part 2

This is the simplest example of the SQL Server Cursor. I have used this all the time for any use of Cursor in my T-SQL.
DECLARE @AccountID INT
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR FOR
SELECT Account_ID
FROM Accounts
OPEN @getAccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @AccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
END
CLOSE @getAccountID
DEALLOCATE @getAccountID

What to Check Before You Reuse This Simple Example of Cursor

The pattern has four steps that always travel together: declare, open, fetch in a loop, then close and deallocate. If you copy it for your own work, keep all four. A cursor that is opened and never closed keeps holding its resources until the batch ends or the connection closes.

The WHILE loop depends on @@FETCH_STATUS. A value of 0 means the fetch worked and returned a row. When there are no more rows, it becomes -1 and the loop stops. Notice that FETCH NEXT appears twice: once before the loop and once at the end of the loop body. If you forget the second one, the loop never moves to the next row and runs forever.

A few habits that help:

• When you only read rows from start to end, ask for a FAST_FORWARD cursor. It is read only, forward only and the lightest choice.
• Make the variable type match the column type, so values are not converted or cut short.
• Test the loop on a small table first, and print a counter so you can see the progress.

The bigger question is whether you need a cursor at all. SQL Server works best when one statement handles many rows together. An UPDATE with a join, or one INSERT ... SELECT, usually finishes far faster than a loop that touches one row at a time.

I still use cursors for work that is truly row by row, like running a stored procedure once for each database or each account. For those jobs this pattern is clear, and it is easy to debug because you can print each value as it goes by.

If the work inside the loop can fail, wrap it in TRY...CATCH on SQL Server 2005 and later, and make sure the close and deallocate steps also run when an error happens. That way one bad row does not leave the cursor open behind you.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.