Home > T-SQL > SQL SERVER – Get Fetch status of a cursor using @@FETCH_STATUS function

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

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

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: