Cursors
Learn how to iterate a group of rows using cursors.
We'll cover the following
Data manipulation commands like SELECT
, UPDATE
, and DELETE
always work with groups of rows. We can filter using a WHERE
clause if we want to process a specific row. However, there might be times we want to process each row sequentially, such as when we’re iterating an array. Unfortunately, there are no for
loops in T-SQL. What we do have at our disposal, however, are T-SQL cursors.
T-SQL cursor
A cursor is a temporary object which allows us to iterate through the rows of a SELECT
statement. It is very similar to a foreach
loop in other programming languages.
Syntax
There is a specific procedure for creating and using cursors, which requires several steps and T-SQL commands:
-
Every cursor is based on some
SELECT
query whose result will be iterated through. ThisSELECT
query is indicated when the cursor is first declared:DECLARE CursorName CURSOR FOR [SELECT query]
-
To be able to read data from target rows using a cursor, we need to open it first:
OPEN CursorName
-
Before entering the loop, we read the first row. The order and the type of variables into which we write the data must match the order of rows returned by the
SELECT
query of the cursor:FETCH NEXT FROM CursorName INTO [Variables for holding the values of the current row]
-
Then, we iterate while there are still rows to read. The
@@FETCH_STATUS
is a global variable that stores information about whether there are any more rows left in the cursor. Its value is0
if there is still data to read. Therefore, while the value of@@FETCH_STATUS
is equal to0
, we can continue iterating the cursor:WHILE @@FETCH_STATUS = 0 BEGIN [Process the row] -- Fetch the next row FETCH NEXT FROM CursorName INTO [Variables for holding the values of the current row] END
Note: It is crucial not to forget to fetch the next row inside the
WHILE
loop above. Otherwise, we’ll be stuck inside an infinite loop. -
After finishing our task, we must remember to close the cursor and deallocate it from the memory:
CLOSE CursorName DEALLOCATE CursorName
Following the five steps above yields the following complete syntax for creating and using a T-SQL cursor:
DECLARE CursorName CURSOR FOR [SELECT query]
OPEN CursorName
FETCH NEXT FROM CursorName INTO [Variables for holding the values of the current row]
WHILE @@FETCH_STATUS = 0
BEGIN
[Process the row]
-- Fetch the next row
FETCH NEXT FROM CursorName INTO [Variables for holding the values of the current row]
END
CLOSE CursorName
DEALLOCATE CursorName
Example
Let’s look at an example to grasp the cursor concept better. Let’s say we have the following table.
Get hands-on with 1200+ tech skills courses.