Home > T-SQL > SQL SERVER – Get Fetch status of a cursor using @@FETCH_STATUS function

SQL SERVER – Get Fetch status of a cursor using @@FETCH_STATUS function

@@FETCH_STATUS: This function is used to get the current fetch status of a latest opened cursor.This function is global function for all cursors in the application and it is non-deterministic. Because, the result is unpredictable.

For instance, a user executes a FETCH statement from one cursor, and then calls a stored procedure that opens and processes the results from another cursor. When control is returned from the called stored procedure, @@FETCH_STATUS reflects the last FETCH executed in the stored procedure, not the FETCH statement executed before the stored procedure is called.

Function Syntax


It returns an integer value as given below.

0 = The FETCH statement was successful.

-1 = The FETCH statement failed or the row was beyond the result set or end of record.

-2 = The row fetched is missing.


This is a simple example read the all rows in the cursor variable.


--Create a cursor.
    SELECT Top 5 [FirstName] FROM [AdventureWorks2012].[Person].[Person]

-- opening the cursor
OPEN person
SELECT CURSOR_STATUS('global','person') AS 'isReady?'

fetch next from person;
while @@FETCH_STATUS = 0
    fetch next from person;

--closing the cursor.
CLOSE person
SELECT abs(CURSOR_STATUS('global','person')) AS 'isCursorClosed?'

--Remove the cursor from memory.

Outputpic1The latest fetch status of all cursors can be obtained from the DMV sys.dm_exec_cursors.

Example DMV query

select * from sys.dm_exec_cursors (@@SPID)

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: