In SQL Server, a CURSOR is a database object used to retrieve data from a result set one row at a time.

While standard SQL is “set-based” (operating on all rows at once), a cursor is “procedural,” acting like a FOR EACH loop in traditional programming. Because cursors are memory-intensive and slower than set-based operations, they should only be used when row-by-row logic is strictly necessary.

1. The Lifecycle of a Cursor

To use a cursor, you must follow these five steps in order:

  1. DECLARE: Define the SQL query that will populate the cursor.
  2. OPEN: Execute the query and populate the cursor with data.
  3. FETCH: Retrieve a specific row into variables.
  4. CLOSE: Release the current result set but keep the definition.
  5. DEALLOCATE: Completely remove the cursor definition from memory.

2. Detailed Example

This example loops through an Employees table to print a custom message for each person.

-- 1. Declare variables to hold row data
DECLARE @EmpName NVARCHAR(100);
DECLARE @EmpSalary MONEY;

-- 2. Declare the Cursor
DECLARE emp_cursor CURSOR FOR 
SELECT Name, Salary FROM Employees;

-- 3. Open the Cursor
OPEN emp_cursor;

-- 4. Fetch the first row
FETCH NEXT FROM emp_cursor INTO @EmpName, @EmpSalary;

-- 5. Loop through the rows
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Processing: ' + @EmpName + ' with salary ' + CAST(@EmpSalary AS VARCHAR);
    
    -- Fetch the next row
    FETCH NEXT FROM emp_cursor INTO @EmpName, @EmpSalary;
END;

-- 6. Clean up
CLOSE emp_cursor;
DEALLOCATE emp_cursor;

3. Important System Functions

  • @@FETCH_STATUS: This is the most critical function.
  1. Returns 0: Fetch was successful.
  2. Returns -1: Fetch failed or reached the end of the list.
  3. Returns -2: The row being fetched is missing.

4. Cursor Types

When declaring a cursor, you can specify how it behaves:

  • FORWARD_ONLY: The cursor can only move from the first row to the last. This is the fastest type.
  • SCROLL: Allows you to move backward (FETCH PRIOR), jump to the start (FETCH FIRST), or move to specific positions.
  • STATIC: Makes a temporary copy of the data in tempdb. Changes made to the original table while the cursor is open won’t be seen.
  • DYNAMIC: Reflects all changes made to the underlying rows while the cursor is open.

i. Static Cursors

A static cursor makes a complete copy of the result set into the tempdb database. It is essentially a “snapshot” of the data at the exact moment the cursor was opened.

  • Behavior: It does not see any changes (updates, deletes, or inserts) made to the original tables while the cursor is open.
  • Best For: When you need to perform multiple passes over the same data without worrying about external changes.

Example:

DECLARE static_cursor CURSOR STATIC FOR
SELECT ProductName, UnitPrice FROM Products;

ii. Dynamic Cursors

A dynamic cursor is the opposite of a static one. It reflects all data changes made to the result set as you scroll through it.

  • Behavior: If another user updates a row’s price while your cursor is running, you will see the new price when you fetch that row. It even reflects new rows inserted into the table.
  • Best For: Applications that require real-time data accuracy, though it is the most resource-heavy option.

Example:

DECLARE dynamic_cursor CURSOR DYNAMIC FOR
SELECT ProductName, UnitPrice FROM Products;

iii. Forward-Only Cursors

This is the default cursor type. It only supports fetching rows in order from the first row to the last.

  • Behavior: You cannot move backward (no FETCH PRIOR).
  • Performance: It is the fastest and most efficient type because SQL Server does not have to keep track of previous rows in memory.
  • Note: All changes made by others that affect the current or future rows in the cursor are visible.

Example:

DECLARE forward_cursor CURSOR FORWARD_ONLY FOR
SELECT ProductName FROM Products;

iv. Keyset-Driven Cursors

This is a hybrid approach. When opened, it builds a list of “keys” (unique identifiers) in tempdb for all matching rows.

  • Behavior:
  1. Updates: If someone updates a row, you will see the change (because the cursor uses the key to fetch the latest data).
  2. Inserts: You cannot see new rows (because their keys weren’t in the initial list).
  3. Deletes: If a row is deleted, a fetch returns an @@FETCH_STATUS of -2 (row is missing).

Example:

DECLARE keyset_cursor CURSOR KEYSET FOR
SELECT ProductID, ProductName FROM Products;

Categorized in:

Stored Procedures,