Cursors

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:

1. Every cursor is based on some SELECT query whose result will be iterated through. This SELECT query is indicated when the cursor is first declared:

   DECLARE CursorName CURSOR FOR [SELECT query]
   

2. To be able to read data from target rows using a cursor, we need to open it first:

   OPEN CursorName
   

3. 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]
   

4. 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 is 0 if there is still data to read. Therefore, while the value of @@FETCH_STATUS is equal to 0, 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.

5. 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.