Have Cursor, Will Travel

A bright rainbow over our house

When I write software that accesses databases, I usually iterate, or loop through, all of the results.  This is very handy.

Sometimes, when working in SQL, you need to do act on each row of a query.  To do so, we uses cursors. 

A cursor is how the SQL Server keeps track of where in the result set it is.  To use a cursor, you will need to declare at least variables. One variable will hold the cursor, and one variable for each column you want to play with.

Let's say, for example, we need to add a counting integer to every row.

First we declare our cursor and our variable to hold our primary key.  I'm also declaring an integer to hold our counter.

DECLARE @aCursor as CURSOR;
DECLARE @anID as INT;

DECLARE @aCounter as INT; 

Next, we have to define what the cursor is.  Here we set our cursor variable to a query.  The setting statement is in orange and the query is in green.

SET @aCursor = CURSOR FOR
SELECT IDNumber
FROM .[dbo].SomeTable 

Next, we have to open the cursor.  

OPEN @aCursor ;

Then, we need to see if there is a row to process.  The data from the query is put into the a variable (in orange below.)  If you have more than one column, separate them by commas. 

FETCH NEXT FROM @aCursor INTO @anID 

Next, we set up your pretest loop need to see if there is a row to process.  The data from the query is put into the a variable (in orange below.)  The code that repeats for each row is in between the BEGIN and END

WHILE @@FETCH_STATUS = 0
 BEGIN

    -- Increment our Counter

    SET @aCounter=@aCounter+1;

    --Update SomeTable with our counter

    UPDATE Sometable set CountField= @aCounter 

     WHERE IDNumber=@anID ;

    FETCH NEXT FROM @aCursor INTO @anID
  END

Lastly, we need to clean up after ourselves.  First we close and then deallocate the cursor.

CLOSE @aCursor;
DEALLOCATE @aCursor;