Export (0) Print
Expand All

@@FETCH_STATUS (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Returns the status of the last cursor FETCH statement issued against any cursor currently opened by the connection.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions

@@FETCH_STATUS

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.

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. The value of @@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 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.

To retrieve the last fetch status of a specific cursor, query the fetch_status column of the sys.dm_exec_cursors dynamic management function.

The following 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
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft