 |
| A bright rainbow over our house |
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
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;
