CURSOR in Transact SQL

A cursor is a data structure that allows you to go through the set of rows returned by a SELECT and process each record one by one.

A cursor consists of different steps that are collected in the following syntax:

 -- Declaring variables
 DECLARE variables
 -- Declaring Cursor Name
 DECLARE cursorName CURSOR [ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
 -- Select to itirate
 FOR SELECT [ FOR UPDATE [ OF <Column_Name>[ ,…n ] ] ] [;]
 -- Cursor Opening
 OPEN cursorName
 -- Record iteration and insertion into variables
 FETCH NEXT FROM cursorName
 -- Cursor Closing
 CLOSE cursorName
 -- Cursor dellocating
 DEALLOCATE cursorName

Taking into account the different arguments that a cursor can receive, we can categorize them according to:

• Cursor scope.
• Data recovery options.
• Types of cursors.
• Types of constraints.

Cursor scope

• LOCAL: The scope is limited only to the stored procedure, trigger or batch that defined it.

• GLOBAL: The scope is at the connection level, and can be used in any stored procedure, trigger or batch.

Data recovery options

• FORWARD_ONLY: specifies that the cursor can only move forward from the first to the last row. It only allows as a data retrieval option: FETCH NEXT to read a record and go to the next.

• SCROLL provides the following data recovery options:

  1. FIRST
  2. LAST
  3. PRIOR
  4. NEXT
  5. RELATIVE
  6. ABSOLUTE

If the SCROLL argument is omitted, the cursor would be FORWARD_ONLY, allowing only the option NEXT.

Cursor Types

• STATIC: they allow you to move forwards and backwards. These cursors never reflect the changes to the rows, always displaying the result set equal to the time the cursor was opened.

• DYNAMIC: Unlike the static ones, the dynamic cursors will reflect all the modifications made in the rows of the result set.

• FAST_FORWARD: cursor FORWARD_ONLY, READ_ONLY with performance optimizations.

• KEYSET: cursor halfway between a static and a dynamic cursor. This type of cursors will not always detect changes in the membership and order of the result set, but they will detect changes in the values of the rows of the result set.

Lock types

• READ ONLY: Read only, preventing updates through the cursor. This option removes the default ability to update the cursor.

• SCROLL_LOCKS: locks rows as they are read, to ensure data integrity and that UPDATES AND DELETES are successful.

• OPTIMISTIC: does not lock rows as they are read, preventing UPDATES AND DELETES from succeeding if any updates are made outside the cursor.

Example

The following example of cursor, has the objective of reading the records corresponding to the relationship between the table Customers and Orders. We will use its creation to briefly explain the essential steps required.

--Declaring Variables
DECLARE @name VARCHAR (20),
        @lastName VARCHAR (50),
         @product VARCHAR (50),
         @price FLOAT

-- Declaring the cursor name and Select to Iterate
DECLARE cCustomersOrders CURSOR FOR
SELECT c.name,
       c.lastName,
       o.product,
       o.price
FROM Customers c INNER JOIN Orders o
ON c.customerId = o.customerId

-- Cursor opening
OPEN cCustomersOrders

-- Record iteration and insertion into variables
FETCH NEXT FROM cCustomersOrders
INTO  @name,
      @lastName,
      @product,
      @price
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @name,
           @lastName,
           @product,
           @price
    FETCH NEXT FROM cCustomersOrders
    INTO  @name,
          @lastName,
          @product,
          @price
END
-- Cursor closing
CLOSE cCustomersOrders;
-- Cursor deallocating
DEALLOCATE cCustomersOrders;

The returned result, selecting and executing all its instructions is the following:

Let’s move on to a brief explanation of each step of our cursor: cCustomersOrders

STEP 1: Declaring variables

--Declaring Variables
DECLARE @name VARCHAR (20),
        @lastName VARCHAR (50),
        @product VARCHAR (50),
        @price FLOAT

The first step is to declare the necessary variables to fill in with the data returned by the cursor. Note that the data type of the variable must be compatible with the data type of the field whose value you want to store.

STEP 2: Declaring the cursor name and SELECT

-- Declaring the cursor name and Select to Iterate
DECLARE cCustomersOrders CURSOR FOR
SELECT c.name,
       c.lastName,
       o.product,
       o.price
FROM Customers c INNER JOIN Orders o
ON c.customerId = o.customerId

The next step is to declare the name of the cursor and the SELECT that it will execute and then iterate over to loop through each record in the result set. Optionally, this step is subject to the use of various options and arguments that will define the cursor as seen at the beginning of this section.

STEP 3: Cursor opening

-- Cursor opening
OPEN cCustomersOrders

STEP 4: Iterating records and inserting into variables

-- Record iteration and insertion into variables
FETCH NEXT FROM cCustomersOrders
INTO  @name,
      @lastName,
      @product,
      @price
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @name,
           @lastName,
           @product,
           @price
    FETCH NEXT FROM cCustomersOrders
    INTO  @name,
          @lastName,
          @product,
          @price
END

The next step consists of iterating through the records obtained in the query and inserting the returned data into the declared variables. The loop works as follows:

• FETCH is a command that places the cursor at a position. Once the first NEXT record has been read and processed, it places the cursor on the next row for processing.

• Once the first record is processed, if everything goes well, the variables will be filled with the values returned by the query while the FETCH_STATUS function will return a 0, indicating that the row has been processed correctly.

• As long as FETCH_STATUS returns 0, each record will be read and processed in a loop until the last row of the result set is iterated over.

STEP 5: Cursor closing

-- Cursor closing
CLOSE cCustomersOrders;

Once the last row has been processed, it is necessary to exit the cursor by closing it. This way, the result set cannot be traversed again until it is reopened.

STEP 6: Cursor Deallocating

--Cursor Deallocating
DEALLOCATE cCustomersOrders;

The last step will be to remove the definition of the cursor and release all system resources associated with it, removing it from the memory.