| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | ||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
FETCH_STATUS (Transact-SQL) |
@@FETCH_STATUS (Transact-SQL) |
markingmyname |
maghan |
09/18/2017 |
sql |
t-sql |
reference |
|
|
|
[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]
This function returns the status of the last cursor FETCH statement issued against any cursor currently opened by the connection.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
@@FETCH_STATUS
integer
| 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. |
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.
This example uses @@FETCH_STATUS to control cursor activities in a WHILE loop.
DECLARE Employee_Cursor CURSOR FOR
SELECT BusinessEntityID, JobTitle
FROM AdventureWorks2022.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