Cursors In SQL Server

Estimated study time: 11 minutes. Process a result set one row at a time.

A cursor lets you step through a result set row by row, rather than operating on the whole set at once. SQL is set-based by nature, so cursors are the exception, not the default tool.

Basic Cursor Syntax

DECLARE @EmpName VARCHAR(100);

DECLARE emp_cursor CURSOR FOR
SELECT EmpName FROM Employees;

OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @EmpName;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @EmpName;
    FETCH NEXT FROM emp_cursor INTO @EmpName;
END;

CLOSE emp_cursor;
DEALLOCATE emp_cursor;

Cursor Types

SQL Server supports several cursor types, including STATIC (a snapshot, insensitive to later changes), DYNAMIC (reflects changes as you scroll), and FORWARD_ONLY (can only move forward, and is the fastest of the three).

Why Cursors Are Usually a Last Resort

Cursors process one row at a time, which is far slower than a set-based UPDATE, INSERT, or SELECT working on the whole table at once. Most row-by-row logic can be rewritten as a single set-based statement.