A cursor in SQL Server is a way to process query results one row at a time, like a foreach loop over a result set. Most of the time, set-based SQL (one statement that affects many rows) is faster and simpler. I recommend using cursors only when you genuinely need per-row logic (calling a stored procedure per row, complex conditional logic that’s hard to express set-based, interacting with external systems, etc.).

Mental model

Think of a cursor as having a pointer that moves across rows returned by a SELECT. You typically do: declare → open → fetch loop → close → deallocate.

The minimal working pattern

DECLARE @Id int, @Name nvarchar(100);
DECLARE cur CURSOR LOCAL FAST_FORWARD FOR

SELECT Id, Name
	FROM dbo.Users
	WHERE IsActive = 1;

OPEN cur;

FETCH NEXT FROM cur INTO @Id, @Name;

WHILE @@FETCH_STATUS = 0
BEGIN
	-- Per-row work goes here
	PRINT CONCAT('User: ', @Id, ' ', @Name);
	FETCH NEXT FROM cur INTO @Id, @Name;
END

CLOSE cur;
DEALLOCATE cur;

I recommend treating this as the baseline template and only changing options when you know why.

The core keywords explained

A cursor has 4 main phases.

DECLARE cur CURSOR ... FOR SELECT ...

This defines the cursor and what rows it will iterate over.

OPEN cur

This executes the SELECT and makes the cursor ready.

FETCH NEXT FROM cur INTO ...

This moves the pointer and copies the current row’s columns into variables.

CLOSE cur
DEALLOCATE cur

CLOSE releases the active result set. DEALLOCATE destroys the cursor definition and frees resources. I recommend always doing both.

Loop control: @@FETCH_STATUS

@@FETCH_STATUS is a system value that tells you whether the last FETCH worked.

It is 0 when the fetch succeeded, and non-zero when it didn’t (for example, you ran out of rows). The common loop pattern is: fetch once, then WHILE @@FETCH_STATUS = 0, then fetch again at the end of the loop.

Cursor options (how to choose quickly)

Cursors have lots of types. As a beginner, I recommend this rule: start with LOCAL FAST_FORWARD unless you need something else.

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR ...

FAST_FORWARD is essentially “read-only, forward-only” and is often the most efficient cursor style for simple iteration. LOCAL means it only exists in your current scope (stored procedure / batch), which is usually what you want.

Cursor “scope”: LOCAL vs GLOBAL

LOCAL means the cursor only exists within the current batch/procedure. GLOBAL means it can be visible outside that scope. I recommend LOCAL almost always because global cursors become confusing and leak-y fast.

Read-only vs updatable cursors

Most cursors should be read-only (you read rows, then do set-based updates elsewhere). If you really need to update through a cursor, SQL Server supports that, but it’s easy to misuse.

The usual beginner approach is: fetch row → run an UPDATE by key.

UPDATE dbo.Users
SET LastSeenUtc = SYSUTCDATETIME()
WHERE Id = @Id;

This is typically clearer than trying to do positioned updates.

Example: calling a stored procedure per row

This is one of the most common legitimate cursor uses.

DECLARE @OrderId int;

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
	SELECT OrderId
	FROM dbo.Orders
	WHERE Status = 'Pending';
	
OPEN cur;

FETCH NEXT FROM cur INTO @OrderId;

WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC dbo.ProcessOrder @OrderId = @OrderId;
	FETCH NEXT FROM cur INTO @OrderId;
END

CLOSE cur;
DEALLOCATE cur;

Debugging tips

If the loop never runs, it usually means the first FETCH didn’t succeed because the SELECT returned zero rows. I recommend temporarily checking the cursor query by running the SELECT alone first, then re-run with the cursor.

If your variables look wrong, it usually means the SELECT column order doesn’t match the INTO variable order.

Cleanup safety: TRY/CATCH pattern (recommended)

If an error happens mid-loop, you still want to close/deallocate.

DECLARE @Id int;

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
	SELECT Id FROM dbo.Users;

BEGIN TRY
	OPEN cur;
	
	FETCH NEXT FROM cur INTO @Id;
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Work
		FETCH NEXT FROM cur INTO @Id;
	END
	
	CLOSE cur;
	DEALLOCATE cur;
END TRY
BEGIN CATCH
	IF CURSOR_STATUS('local', 'cur') >= -1
	BEGIN
		CLOSE cur;
		DEALLOCATE cur;
	END
	
	THROW;
END CATCH

CURSOR_STATUS helps you avoid errors like closing a cursor that was never opened.

Cursor_STATUS quick reference

SELECT CURSOR_STATUS('local', 'cur');

You’ll typically see values like “doesn’t exist”, “declared but not open”, or “open”. You mainly use it to decide whether cleanup is safe inside CATCH.

When I recommend NOT using a cursor

If you’re doing something like “for each row, update another table”, it’s often better as a single UPDATE ... FROM ... or MERGE, or an INSERT ... SELECT. Cursors are slower because they keep hopping row-by-row and can hold locks longer.

If you tell me what you’re trying to achieve (even roughly), I can usually suggest the set-based alternative—or confirm a cursor is the right tool.