DECLARE CURSOR (Transact‑SQL)

Synopsis

DECLARE cursor_name CURSOR
FOR
    select_statement
[FOR UPDATE OF column_name [,...]]
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
[TYPE_WARNING]

Description

The DECLARE CURSOR statement creates a cursor that can be used to iterate over rows returned by a SELECT statement. Cursors enable row‑by‑row processing when set‑based operations are insufficient.

Key options:

STATIC – Snapshot of the result set; insensitive to changes.
KEYSET – Keys are fixed; values reflect updates.
DYNAMIC – Fully dynamic; reflects all changes.
FAST_FORWARD – Optimized read‑only forward‑only cursor.
READ_ONLY – Prevents updates via the cursor.
SCROLL_LOCKS – Allows scrollable cursor with row‑locking.
OPTIMISTIC – Uses optimistic concurrency.

Syntax Details

Clause	Description
`cursor_name`	Name of the cursor (must be unique within the batch).
`FOR`	Introduces the `SELECT` statement that defines the result set.
`FOR UPDATE OF`	Specifies columns that can be updated using `UPDATE WHERE CURRENT OF`.
`STATIC`	Creates a static copy of the result set.
`KEYSET`	Result set uses a fixed set of keys.
`DYNAMIC`	Result set reflects all changes made to underlying tables.
`FAST_FORWARD`	Optimized for forward‑only, read‑only cursors.
`READ_ONLY`	Prevents updates via the cursor.
`SCROLL_LOCKS`	Supports scrolling with row‑level locks.
`OPTIMISTIC`	Enables optimistic concurrency control.

Examples

Example 1 – Simple cursor

USE AdventureWorks2019;
GO
DECLARE @ProductID int, @Name nvarchar(50);
DECLARE product_cursor CURSOR FOR
    SELECT ProductID, Name
    FROM Production.Product
    WHERE ListPrice > 1000;
OPEN product_cursor;
FETCH NEXT FROM product_cursor INTO @ProductID, @Name;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT CONCAT('Product ', @ProductID, ': ', @Name);
    FETCH NEXT FROM product_cursor INTO @ProductID, @Name;
END
CLOSE product_cursor;
DEALLOCATE product_cursor;
GO

This script iterates over all products with a list price above 1000 and prints each product ID and name.

Example 2 – Cursor with FAST_FORWARD

DECLARE @EmployeeID int;
DECLARE emp_cursor CURSOR FAST_FORWARD FOR
    SELECT EmployeeID FROM HumanResources.Employee WHERE HireDate > '2020-01-01';
OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @EmployeeID;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_helptext N'SELECT * FROM HumanResources.Employee WHERE EmployeeID = @EmployeeID';
    FETCH NEXT FROM emp_cursor INTO @EmployeeID;
END
CLOSE emp_cursor;
DEALLOCATE emp_cursor;

Uses a forward‑only, read‑only cursor for better performance.