@@FETCH_STATUS (Transact-SQL)

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

This function returns the status of the last cursor FETCH statement issued against any cursor currently opened by the connection.

Topic link icon Transact-SQL Syntax Conventions

Syntax

@@FETCH_STATUS  

Return Type

integer

Return Value

Return value Description
 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.

Remarks

Because @@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.

Examples

This example uses @@FETCH_STATUS to control cursor activities in a WHILE loop.

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  

See Also

Cursor Functions (Transact-SQL)
FETCH (Transact-SQL)