SQL Server 2025 · Module 6

Cursor Variables and References in SQL Server

Lesson 5

Cursor Variables and References in SQL Server

Objective: Use a cursor variable without confusing it with global scope.

A variable of type CURSOR holds a local reference to an underlying cursor structure. Declaring a cursor variable does not create a global cursor, nor does it require the referenced cursor to have GLOBAL scope.

Create and use a cursor through a variable

Run the Lesson 2 setup first to create and populate #Employees, then execute this example as a single batch.

DECLARE @employee_cursor CURSOR;

SET @employee_cursor = CURSOR LOCAL FAST_FORWARD FOR
    SELECT EmployeeID, EmployeeName
    FROM #Employees
    ORDER BY EmployeeID;

OPEN @employee_cursor;
FETCH NEXT FROM @employee_cursor;
CLOSE @employee_cursor;
DEALLOCATE @employee_cursor; -- Releases the variable reference; deallocates the cursor if no other references remain

Executing FETCH without an INTO clause returns the row directly to the client; adding INTO assigns the column values to compatible local variables instead. Standard cursor operations—OPEN, FETCH, CLOSE, and DEALLOCATE—all accept cursor variable references directly.

A reference is not a second copy

When you assign an existing named cursor to a cursor variable, both identifiers point to the exact same underlying cursor and share its current row position:

DECLARE employee_cursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT EmployeeID, EmployeeName
    FROM #Employees
    ORDER BY EmployeeID;

DECLARE @cv CURSOR;
SET @cv = employee_cursor; -- Both employee_cursor and @cv reference the same underlying cursor

Cursor lifetime is governed by reference counting rather than the original name's scope. Deallocating the named cursor (`DEALLOCATE employee_cursor`) removes that name, but the underlying cursor remains active in memory as long as `@cv` still holds a valid reference to it. Lesson 10 demonstrates this reference-counting lifecycle in detail.

Pass a local cursor out of a procedure

You can pass a cursor reference out of a stored procedure using a CURSOR VARYING OUTPUT parameter, which extends the cursor's lifetime beyond the procedure's execution scope:

CREATE OR ALTER PROCEDURE dbo.OpenEmpCursor
    @OutCrsr CURSOR VARYING OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @OutCrsr = CURSOR LOCAL FAST_FORWARD FOR
        SELECT EmployeeID, EmployeeName
        FROM #Employees
        ORDER BY EmployeeID;

    OPEN @OutCrsr;
END;
GO

DECLARE @cv CURSOR;
EXEC dbo.OpenEmpCursor @OutCrsr = @cv OUTPUT;

FETCH NEXT FROM @cv;

CLOSE @cv;
DEALLOCATE @cv; -- Last reference removed; underlying cursor is deallocated

While OUTPUT parameters allow you to modularize cursor creation, they split cursor ownership and cleanup across routines. Unless cross-procedure sharing is strictly required, keep cursor declaration, consumption, and deallocation within a single routine so cleanup remains explicit.

Key distinctions

  • Strictly local variable scope: A cursor variable (`@cv`) is always a local reference and never has GLOBAL scope itself.
  • Referencing global cursors: Assigning a GLOBAL cursor to a variable creates a local reference to that cursor, but the global cursor name persists until it is explicitly deallocated or the session disconnects.
  • Reference-counted lifetime: An underlying cursor is only deallocated from memory when its final remaining reference (named cursor, local variable, or OUTPUT parameter) is deallocated or goes out of scope.
  • Best practice: Prefer LOCAL cursors assigned to variables or scoped locally over GLOBAL cursor names to prevent naming collisions and unintended state sharing across batches.

Check your understanding

Does a cursor variable make the cursor available to other connections? No. Does DEALLOCATE on one reference necessarily remove all other references? No. Both questions concern reference ownership, not the variable's spelling.

Try the cursor types and scope self-check before continuing.

Microsoft documentation