T-SQL Cursor

Master T-SQL cursors — process result sets one row at a time. Covers the full DECLARE / OPEN / FETCH / CLOSE / DEALLOCATE lifecycle with inline diagram, all five cursor types (STATIC, DYNAMIC, KEYSET, FAST_FORWARD, FORWARD_ONLY), LOCAL vs GLOBAL scope, cursor functions (@@FETCH_STATUS, @@CURSOR_ROWS, CURSOR_STATUS) with corrected return values, set-based alternatives, and best practices.

A cursor in T-SQL is a database object that lets you process the rows of a result set one at a time. Where a normal SQL query operates on the entire set at once, a cursor opens a "cursor" (think: a moving pointer) over the result and fetches rows one by one into local variables — letting you apply procedural logic per row.

Cursors come up when you genuinely need row-by-row work: complex per-row calculations that don't fit in a single SQL expression, conditional sequences of operations per row, or producing output where each row's processing depends on accumulated state from previous rows.

Every T-SQL cursor goes through five stages in strict order:

1. Declare the cursor

cursor_type is optional — if omitted, you get the server's default. The common types (STATIC, DYNAMIC, KEYSET, FAST_FORWARD, FORWARD_ONLY) are covered later in this page.

2. Open the cursor

This is the moment the SELECT actually runs.

3. Fetch a row

The variable list must match the column list in the SELECT — same number, same data types.

4. Process each row in a WHILE loop

5. Close and deallocate

The classic example — walk a department's worth of employees and print each row.

CREATE TABLE Employees (
    EmployeeID  INT PRIMARY KEY,
    FirstName   NVARCHAR(50),
    LastName    NVARCHAR(50),
    Department  NVARCHAR(50),
    Salary      DECIMAL(10, 2)
);

INSERT INTO Employees VALUES
    (101, 'John',     'Doe',      'Sales',     50000.00),
    (102, 'Jane',     'Smith',    'Marketing', 60000.00),
    (103, 'Sam',      'Brown',    'Sales',     55000.00),
    (104, 'Sue',      'Green',    'HR',        65000.00),
    (105, 'Bob',      'White',    'Sales',     52000.00),
    (106, 'Chris',    'Brown',    'Marketing', 85000.00),
    (107, 'Patricia', 'Taylor',   'Sales',     65000.00),
    (108, 'Linda',    'Anderson', 'Marketing', 88000.00),
    (109, 'Robert',   'Thomas',   'HR',        78000.00),
    (110, 'Barbara',  'Jackson',  'Sales',     95000.00);

-- 1. Declare variables to hold each row's columns
DECLARE @EmployeeID  INT,
        @FirstName   NVARCHAR(50),
        @LastName    NVARCHAR(50),
        @Department  NVARCHAR(50),
        @Salary      DECIMAL(10, 2);

-- 2. Declare the cursor
DECLARE employee_cursor CURSOR FOR
    SELECT EmployeeID, FirstName, LastName, Department, Salary
    FROM   Employees
    WHERE  Department = 'Sales';

-- 3. Open it (this is when the SELECT actually runs)
OPEN employee_cursor;

-- 4. Prime the loop with the first FETCH
FETCH NEXT FROM employee_cursor
    INTO @EmployeeID, @FirstName, @LastName, @Department, @Salary;

-- 5. Process each row, fetching the next one at the bottom
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'EmployeeID: ' + CAST(@EmployeeID AS VARCHAR(10)) +
          '  Name: '    + @FirstName + ' ' + @LastName +
          '  Dept: '    + @Department +
          '  Salary: '  + CAST(@Salary AS VARCHAR(10));

    FETCH NEXT FROM employee_cursor
        INTO @EmployeeID, @FirstName, @LastName, @Department, @Salary;
END

-- 6. Always close and deallocate
CLOSE      employee_cursor;
DEALLOCATE employee_cursor;

That cursor walked 5 rows. For 5 rows it's fine — for 5 million it would be painful. Here's the same job done set-based, in a single statement:

SELECT 'EmployeeID: ' + CAST(EmployeeID AS VARCHAR(10)) +
       '  Name: '    + FirstName + ' ' + LastName +
       '  Dept: '    + Department +
       '  Salary: '  + CAST(Salary AS VARCHAR(10)) AS Line
FROM   Employees
WHERE  Department = 'Sales';

Same output, no cursor, no DECLARE/OPEN/FETCH/CLOSE/DEALLOCATE, and the SQL Server optimizer is free to use indexes, parallelism, and other tricks. Whenever you find yourself reaching for a cursor, run this same mental check first.

The cursor type controls how the cursor sees its data over its lifetime — whether changes made by other transactions are visible, whether you can scroll backward, etc. Pick the cheapest type that meets your needs.

Syntax

DECLARE my_cursor CURSOR STATIC       FOR SELECT ...;
DECLARE my_cursor CURSOR DYNAMIC      FOR SELECT ...;
DECLARE my_cursor CURSOR KEYSET       FOR SELECT ...;
DECLARE my_cursor CURSOR FAST_FORWARD FOR SELECT ...;
DECLARE my_cursor CURSOR FORWARD_ONLY FOR SELECT ...;

DECLARE my_cursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT ...;

By default, cursors are GLOBAL — visible to every batch in the connection until the connection ends. This causes naming collisions and makes procedures fragile. Always declare cursors as LOCAL:

DECLARE my_cursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT col1, col2 FROM my_table;

@@FETCH_STATUS

The most important cursor function — tells you what just happened on the most recent FETCH. The WHILE loop's condition almost always uses it.

@@CURSOR_ROWS

Returns the number of rows currently in the open cursor's result set. The behavior varies by cursor type:

CURSOR_STATUS

Lets you inspect a cursor's state by name — useful in defensive code that needs to verify a cursor is in a usable state before working with it:

SELECT CURSOR_STATUS('local', 'my_cursor');     -- for LOCAL cursors
SELECT CURSOR_STATUS('global', 'my_cursor');    -- for GLOBAL cursors

1. Try to avoid them. Almost every cursor I've ever rewritten as set-based SQL has been faster.
2. Always use LOCAL FAST_FORWARD when you do need one — fastest read-only forward-only cursor.
3. Always pair OPEN with CLOSE + DEALLOCATE, even on errors. Use TRY ... CATCH if there's any chance the loop body raises.
4. Don't change the underlying table during cursor iteration unless you've thought carefully about which cursor type you're using — it's easy to skip rows or process them twice.
5. Don't open a cursor inside a loop. If you're tempted to, you almost certainly need a JOIN instead.

Genuine cursor use cases are rarer than they seem, but they exist:

• Calling a stored procedure once per row, when the procedure has side effects (sending email, calling out to a service, writing to a log).
• Building strings or aggregating state where each row's processing depends on the previous row's result, in a way that SUM() OVER () or STRING_AGG() can't express.
• Generating dynamic SQL for each row (rare — usually a sign you should normalize the data instead).

If your case fits one of these, a cursor is fine. If not — keep looking for the set-based answer.

• A cursor is a database object that lets you process query results one row at a time. The lifecycle is always DECLARE → OPEN → FETCH (in a loop) → CLOSE → DEALLOCATE.
• Use WHILE @@FETCH_STATUS = 0 to drive the loop. Remember: 0 = success, -1 = no more rows, -2 = missing row.
• Always declare cursors as LOCAL FAST_FORWARD unless you have a specific reason for a different type — it's the fastest and safest combination.
• Always close and deallocate. CLOSE alone leaves the cursor's definition in memory.
• Cursors are slow. Before writing one, ask yourself whether the same job can be done with a single set-based SELECT, UPDATE, or MERGE. The answer is usually yes.
• Use @@FETCH_STATUS, @@CURSOR_ROWS, and CURSOR_STATUS() to inspect cursor state when you need defensive logic.