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 reference to a cursor. Declaring one does not create a global cursor and does not require the referenced cursor to be global.
Create and use a cursor through a variable
Run the Lesson 2 setup first, then execute this example as one 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;The FETCH without INTO returns a row to the client. Adding INTO would assign the columns to compatible variables instead. OPEN, FETCH, CLOSE, and DEALLOCATE accept cursor-variable references.
A reference is not a second copy
When a variable is assigned an existing named cursor, both references identify the same underlying cursor and position. Removing the name does not necessarily destroy the cursor if the variable still references it. Lesson 10 demonstrates that distinction.
Cursor OUTPUT parameters can pass references between procedures, but they also make ownership and cleanup harder to follow. For this introductory module, keep the cursor within one routine and make cleanup explicit.
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.