Transact-SQL Cursors

Transact-SQL cursors enable row-by-row processing of the result set of a Transact-SQL statement. While not generally recommended for performance reasons, cursors are sometimes necessary for complex logic that cannot be handled efficiently by set-based operations.

Note: For most scenarios, set-based operations (like SELECT, INSERT, UPDATE, DELETE) are significantly more efficient than cursors. Explore alternatives before implementing cursors.

When to Use Cursors

Cursors are typically used when:

  • You need to perform complex row-by-row operations based on conditions that are difficult to express in a single set-based statement.
  • You need to update or process rows in a specific order and the order depends on calculations involving previous rows.
  • You are migrating application logic from older systems that rely on row-by-row processing.

Cursor Concepts

Key concepts related to Transact-SQL cursors include:

  • Declaration: Defining a cursor to specify the result set and its properties.
  • Opening: Executing the SELECT statement associated with the cursor and populating it with data.
  • Fetching: Retrieving one row at a time from the cursor's result set into variables.
  • Updating/Deleting: Optionally updating or deleting the current row the cursor is positioned on.
  • Closing: Releasing the current result set and freeing resources.
  • Deallocating: Removing the cursor definition.

Cursor Declaration

The DECLARE CURSOR statement is used to declare a cursor. The syntax allows for various options to control cursor behavior:

DECLARE cursor_name CURSOR
    [ [ INSENSITIVE | SENSITIVE | STATIC ]
      [ SCROLL | NO SCROLL ] ]
    FOR select_statement
    [ FOR UPDATE [ OF column_name [ ,...n ] ] ];
  • INSENSITIVE: Creates a temporary copy of the data. Changes to the underlying table are not reflected.
  • SENSITIVE: Reflects changes made to the underlying tables.
  • STATIC: A snapshot of the data. No changes are reflected.
  • SCROLL: Allows fetching rows in any direction (FIRST, LAST, PRIOR, NEXT, ABSOLUTE, RELATIVE).
  • NO SCROLL: Only allows fetching rows in forward direction (NEXT).
  • FOR UPDATE OF ...: Specifies which columns can be updated through the cursor.

Opening and Fetching

Use OPEN to execute the cursor's query and FETCH to retrieve rows.

OPEN cursor_name;

FETCH NEXT FROM cursor_name INTO @variable1, @variable2, ...;

-- Loop through the cursor
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Process fetched row (using @variable1, @variable2)

    FETCH NEXT FROM cursor_name INTO @variable1, @variable2, ...;
END;

@@FETCH_STATUS indicates the status of the last FETCH statement:

  • 0: FETCH succeeded.
  • -1: FETCH preceded the first row or followed the last row.
  • -2: The FETCH statement is missing or the cursor is not open.

Updating and Deleting with Cursors

If the cursor was declared with FOR UPDATE, you can use UPDATE ... WHERE CURRENT OF ... or DELETE ... WHERE CURRENT OF ....

-- After a successful FETCH
UPDATE YourTable
SET SomeColumn = 'New Value'
WHERE CURRENT OF cursor_name;

DELETE YourTable
WHERE CURRENT OF cursor_name;

Closing and Deallocating

CLOSE releases the result set, and DEALLOCATE removes the cursor definition.

CLOSE cursor_name;
DEALLOCATE cursor_name;

Example: Iterating Through Employees

This example demonstrates a simple cursor to iterate through employee names and their hire dates.

DECLARE @EmployeeName NVARCHAR(100);
DECLARE @HireDate DATE;

DECLARE employee_cursor CURSOR FOR
SELECT Name, HireDate
FROM Employees
WHERE Department = 'Sales'
ORDER BY HireDate;

OPEN employee_cursor;

FETCH NEXT FROM employee_cursor INTO @EmployeeName, @HireDate;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Employee: ' + @EmployeeName + ', Hired on: ' + CONVERT(VARCHAR, @HireDate, 101);

    -- Example of updating the current row (if declared FOR UPDATE)
    -- UPDATE Employees
    -- SET Notes = 'Processed via cursor'
    -- WHERE CURRENT OF employee_cursor;

    FETCH NEXT FROM employee_cursor INTO @EmployeeName, @HireDate;
END;

CLOSE employee_cursor;
DEALLOCATE employee_cursor;
Tip: Always close and deallocate cursors when you are finished with them to release resources.

Cursor Types

Implementation Cursors

SQL Server determines how to implement the cursor based on the options specified in the DECLARE CURSOR statement. The options specify the behavior, not the underlying implementation.

API Cursors

These are the cursors that are generally visible to the application, such as ODBC, OLE DB, or ADO cursors.

Global vs. Local Cursors

By default, Transact-SQL cursors are local to the batch, stored procedure, or trigger in which they are created. You can use SET CURSOR_CLOSE_ON_COMMIT ON to have cursors automatically closed when a transaction commits.

Performance Warning: Extensive use of cursors can lead to performance degradation due to context switching between the application and SQL Server and increased locking. Always measure performance and consider alternatives.