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

@@FETCH_STATUS

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.

Example

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

SQL

--Create a cursor.
DECLARE person CURSOR  FOR 
    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
begin
    fetch next from person;
end

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

--Remove the cursor from memory.
DEALLOCATE person

OutputThe 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)