A Better Cursor Pattern for SQL Server: Cursor Variables and @@CURSOR_ROWS

Yes, I know. Cursors. The word alone makes some DBAs flinch. Set-based operations are almost always the right answer, and if you can avoid a cursor entirely, you should.

But sometimes you genuinely need one. Generating dynamic scripts, processing rows with side effects that cannot be batched, walking a hierarchy with conditional logic at each step. When that happens, you might as well use a cursor pattern that is clean, readable, and less prone to the classic “forgot the priming fetch” bug.

The Traditional Pattern

This is the cursor loop most of us learned first:

It works, but there are a few things that bother me about it.

The duplicated FETCH NEXT. You need one before the loop (the “priming fetch”) and one at the end of the loop body. If someone edits the column list in one FETCH but not the other, you get a runtime error. If someone removes the priming fetch during refactoring, the loop never executes. It is a maintenance trap.

@@FETCH_STATUS is global. It reflects the status of the last fetch operation on any cursor in the session. If you have nested cursors or call a stored procedure that uses its own cursor inside your loop, @@FETCH_STATUS can be overwritten by the inner cursor’s fetch. This leads to subtle bugs that only appear under specific conditions.

The cursor name is global. login_cursor lives in the session’s global cursor namespace. If a stored procedure you call happens to use the same cursor name, you get a collision. You can work around this with LOCAL, but it is easy to forget.

A Cleaner Pattern: Cursor Variables and @@CURSOR_ROWS

Here is the pattern I prefer:

No priming fetch. No duplicated FETCH NEXT. No @@FETCH_STATUS.

The FETCH NEXT appears exactly once, at the top of the loop body, and the loop terminates based on a simple counter that you control.

Why This Works

The key is the STATIC cursor option. When you open a STATIC cursor, SQL Server materializes the entire result set into a work table in tempdb. Once that snapshot is taken, @@CURSOR_ROWS returns the exact row count.

We capture that count immediately after OPEN, then decrement it with each iteration. When the counter reaches zero, we are done. No ambiguity, no global state dependency.

The Cursor Options Explained

The four options in LOCAL FORWARD_ONLY STATIC READ_ONLY each serve a specific purpose:

LOCAL scopes the cursor to the current batch or stored procedure. It cannot collide with cursors in other procedures or batches, and it is automatically deallocated when the batch ends. Always use LOCAL unless you have a specific reason to share a cursor across batches (you almost certainly do not).

FORWARD_ONLY tells SQL Server you will only move forward through the result set, never backward. This allows the engine to optimize for sequential access. Since our loop only does FETCH NEXT, we never need to scroll backward.

STATIC materializes the result set into a work table in tempdb at OPEN time. This is what makes @@CURSOR_ROWS return an accurate count. It also means the cursor sees a snapshot of the data as it existed when opened; concurrent changes to the underlying tables do not affect the cursor. For script-generation and reporting scenarios, this is exactly what you want.

READ_ONLY tells SQL Server you will not perform positioned updates or deletes through the cursor (UPDATE … WHERE CURRENT OF). This lets the optimizer skip acquiring update locks on the underlying data. If you are just reading rows to generate output, there is no reason to pay the locking overhead.

When to Use Each Option

Not every cursor needs all four options. Here is a quick reference:

The most common case, generating output or performing row-by-row logic that does not need positioned updates, is well served by LOCAL FORWARD_ONLY STATIC READ_ONLY.

When NOT to Use Cursors

Before reaching for any cursor pattern, ask whether you actually need one. In most cases, you do not.

Set-based alternatives are almost always faster. If you are updating rows, inserting rows, or computing aggregates, write it as a single statement. The optimizer can parallelize set-based operations; it cannot parallelize a cursor loop. For help converting existing cursors to set-based logic, see Eliminating Cursors with AI-Generated Set-Based Alternatives.

Recursive CTEs handle hierarchical traversal without cursors.

STRING_AGG or FOR XML PATH can concatenate values without looping.

CROSS APPLY with a table-valued function can replace many row-by-row processing patterns.

Cursors are appropriate when each row requires conditional side effects (sending emails, dynamic SQL with varying structure, calling external procedures), when you need to generate human-readable scripts, or when the processing logic at each row is too complex to express in a single set-based statement.

A Word About @@CURSOR_ROWS vs @@FETCH_STATUS

These two approaches are not interchangeable.

@@CURSOR_ROWS only returns an accurate positive count for STATIC and KEYSET cursors. For DYNAMIC and FAST_FORWARD cursors, it returns -1 (the count is not known because the result set is not materialized). If you are using a DYNAMIC or FAST_FORWARD cursor, stick with @@FETCH_STATUS.

The advantage of @@CURSOR_ROWS is that it is set once, locally, and you control it with a simple decrement. There is no risk of another cursor in a nested call overwriting your loop condition. It also makes the code easier to read: the loop says “process this many rows,” which is a clearer statement of intent than “keep going until the last fetch failed.”

Wrapping Up

Cursors will never be the first tool I reach for, but when I do need one, I want the pattern to be clean and resistant to copy-paste errors. The cursor variable approach with LOCAL FORWARD_ONLY STATIC READ_ONLY and @@CURSOR_ROWS gives me that: one FETCH, no global state, and a loop condition I can reason about at a glance.

If you have a cursor pattern you prefer, I would like to hear about it. You can find me on Bluesky and LinkedIn.