SQL Server 2025 · Module 6

When to Use a SQL Server Cursor

Lesson 2

When to Use a SQL Server Cursor

Objective: Compare cursor processing with set-based SQL and prepare a small practice dataset.

A cursor maintains a position within a query result. FETCH returns a row and can assign its column values to local variables. The program then performs work using those values before requesting another row.

Does the task need a cursor?

Updating all matching rows is usually a set-based task. For example, an UPDATE can change a department code for every employee satisfying a condition. A cursor adds repeated fetches and per-row work; it should solve an actual procedural requirement rather than compensate for an unfamiliar JOIN.

A controlled administrative script that must call an existing procedure separately for each selected identifier can be a reasonable cursor use. Consider failure handling, transaction boundaries, and whether the called procedure supports a set of identifiers instead. Replacing a cursor with a WHILE loop does not automatically make row-by-row processing efficient.

Create the module's practice data

Run this setup once in a new query window. The temporary table belongs to your session. It contains no production employee information.

CREATE TABLE #Employees (
    EmployeeID int NOT NULL PRIMARY KEY,
    EmployeeName nvarchar(50) NOT NULL
);
INSERT INTO #Employees (EmployeeID, EmployeeName)
VALUES (101, N'Ana'), (102, N'Ben'), (103, N'Chen');
EmployeeIDEmployeeName
101Ana
102Ben
103Chen

The primary key provides distinct identifiers. The cursor query will use ORDER BY EmployeeID so that examples referring to the first or second row have a defined order. A table alone does not guarantee retrieval order.

A complete first example

After the setup above, run this entire batch. PRINT is used only to make the lesson observable; producing a report normally needs a single SELECT instead.

DECLARE employee_cursor CURSOR
LOCAL FAST_FORWARD
FOR SELECT EmployeeID, EmployeeName
    FROM #Employees
    ORDER BY EmployeeID;
DECLARE @EmployeeID int, @EmployeeName nvarchar(50);
OPEN employee_cursor;
FETCH NEXT FROM employee_cursor INTO @EmployeeID, @EmployeeName;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT CONCAT(@EmployeeID, N': ', @EmployeeName);
    FETCH NEXT FROM employee_cursor INTO @EmployeeID, @EmployeeName;
END;
CLOSE employee_cursor;
DEALLOCATE employee_cursor;

The expected messages are 101: Ana, 102: Ben, and 103: Chen. The final failed fetch ends the loop without processing stale variable values. These are expected teaching results, not a captured execution trace.

Account for cleanup

CLOSE ends the open result set; DEALLOCATE releases the named reference. The example is intentionally short. Lesson 10 adds error-path cleanup, which matters when real work can raise an exception. When you finish the module session, run DROP TABLE #Employees; or disconnect.

Microsoft documentation