CURSOR_STATUS (Transact-SQL)
Collapse the table of content
Expand the table of content

CURSOR_STATUS (Transact-SQL)

 

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

A scalar function that allows the caller of a stored procedure to determine whether or not the procedure has returned a cursor and result set for a given parameter.

Topic link icon Transact-SQL Syntax Conventions

  
CURSOR_STATUS   
     (  
          { 'local' , 'cursor_name' }   
          | { 'global' , 'cursor_name' }   
          | { 'variable' , 'cursor_variable' }   
     )  

'local'
Specifies a constant that indicates the source of the cursor is a local cursor name.

'cursor_name'
Is the name of the cursor. A cursor name must conform to the rules for identifiers.

'global'
Specifies a constant that indicates the source of the cursor is a global cursor name.

'variable'
Specifies a constant that indicates the source of the cursor is a local variable.

'cursor_variable'
Is the name of the cursor variable. A cursor variable must be defined using the cursor data type.

smallint

Return valueCursor nameCursor variable
1The result set of the cursor has at least one row.

For insensitive and keyset cursors, the result set has at least one row.

For dynamic cursors, the result set can have zero, one, or more rows.
The cursor allocated to this variable is open.

For insensitive and keyset cursors, the result set has at least one row.

For dynamic cursors, the result set can have zero, one, or more rows.
0The result set of the cursor is empty.*The cursor allocated to this variable is open, but the result set is definitely empty.*
-1The cursor is closed.The cursor allocated to this variable is closed.
-2Not applicable.Can be:

No cursor was assigned to this OUTPUT variable by the previously called procedure.

A cursor was assigned to this OUTPUT variable by the previously called procedure, but it was in a closed state upon completion of the procedure. Therefore, the cursor is deallocated and not returned to the calling procedure.

There is no cursor assigned to a declared cursor variable.
-3A cursor with the specified name does not exist.A cursor variable with the specified name does not exist, or if one exists it has not yet had a cursor allocated to it.

*Dynamic cursors never return this result.

The following example uses the CURSOR_STATUS function to show the status of a cursor before and after it is opened and closed.

CREATE TABLE #TMP  
(  
   ii int  
)  
GO  
  
INSERT INTO #TMP(ii) VALUES(1)  
INSERT INTO #TMP(ii) VALUES(2)  
INSERT INTO #TMP(ii) VALUES(3)  
  
GO  
  
--Create a cursor.  
DECLARE cur CURSOR  
FOR SELECT * FROM #TMP  
  
--Display the status of the cursor before and after opening  
--closing the cursor.  
  
SELECT CURSOR_STATUS('global','cur') AS 'After declare'  
OPEN cur  
SELECT CURSOR_STATUS('global','cur') AS 'After Open'  
CLOSE cur  
SELECT CURSOR_STATUS('global','cur') AS 'After Close'  
  
--Remove the cursor.  
DEALLOCATE cur  
  
--Drop the table.  
DROP TABLE #TMP  
  

Here is the result set.

After declare

---------------

-1

After Open

----------

1

After Close

-----------

-1

Cursor Functions (Transact-SQL)
Data Types (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft