This function returns the status of the last cursor FETCH statement issued against any cursor currently opened by the connection.
|0||The FETCH statement was successful.|
|-1||The FETCH statement failed or the row was beyond the result set.|
|-2||The row fetched is missing.|
|-9||The cursor is not performing a fetch operation.|
@@FETCH_STATUS is global to all cursors on a connection, use it carefully. After a FETCH statement executes, the test for
@@FETCH_STATUS must occur before any other FETCH statement executes against another cursor.
@@FETCH_STATUS is undefined before any fetches have occurred on the connection.
For example, a user executes a FETCH statement from one cursor, and then calls a stored procedure that opens and processes results from another cursor. When control returns from that called stored procedure,
@@FETCH_STATUS reflects the last FETCH executed inside that stored procedure, not the FETCH statement executed before the call to the stored procedure.
To retrieve the last fetch status of a specific cursor, query the fetch_status column of the sys.dm_exec_cursors dynamic management function.
This example uses
@@FETCH_STATUS to control cursor activities in a
DECLARE Employee_Cursor CURSOR FOR SELECT BusinessEntityID, JobTitle FROM AdventureWorks2012.HumanResources.Employee; OPEN Employee_Cursor; FETCH NEXT FROM Employee_Cursor; WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM Employee_Cursor; END; CLOSE Employee_Cursor; DEALLOCATE Employee_Cursor; GO