patternsqlMinor

Does @@FETCH_STATUS have (or need) an equivalent to SCOPE_IDENTITY?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

equivalentneeddoesscope_identityfetch_statushave

Problem

MSDN advises that @@FETCH_STATUS be used with care because it's global:

Because @@FETCH_STATUS is global to all cursors on a connection, use @@FETCH_STATUS carefully. After a FETCH statement is executed, the test for @@FETCH_STATUS must occur before any other FETCH statement is executed against another cursor.

This leads me to two questions:

Is it possible that another (not nested) cursor could run concurrently with mine and affect the value of @@FETCH_STATUS between my FETCH NEXT and WHILE condition? For example, say the other cursor is in a stored procedure that's called on a schedule.

If this is possible, does @@FETCH_STATUS have an equivalent of @@IDENTITY's SCOPE_IDENTITY()?

Solution

As long as they are not nested, you should be fine. @@FETCH_STATUS is set at the time the FETCH NEXT is performed. @@FETCH_STATUS is global to the session, not the entire server.

There is no equivalent to SCOPE_IDENTITY() for @@FETCH_STATUS. If you need to nest looped FETCH NEXT operations, make sure that your outer FETCH NEXT is performed after the inner loop completes. If you need to stack FETCH NEXT operations or perform a FETCH NEXT before an inner loop containing a FETCH NEXT, you can DECLARE a temporary variable of type int outside the loop, SET the temporary variable equal to @@FETCH_STATUS immediately after your FETCH NEXT statement, and then use the temporary variable as your loop control instead of @@FETCH_STATUS.

Context

StackExchange Database Administrators Q#129127, answer score: 7

Revisions (0)

No revisions yet.