CURSOR_STATUS (Transact-SQL)

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.

Transact-SQL Syntax Conventions

Syntax

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

Arguments

• '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.

Return Types

smallint

Return value	Cursor name	Cursor variable
1	The 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.
0	The result set of the cursor is empty.*	The cursor allocated to this variable is open, but the result set is definitely empty.*
-1	The cursor is closed.	The cursor allocated to this variable is closed.
-2	Not 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.
-3	A 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.

Examples

This example creates a procedure named lake_list and uses the output from executing lake_list as a check for CURSOR_STATUS.

Note

This example depends on a procedure named check_authority, which has not been created.

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'lake_list' AND type = 'P')
   DROP PROCEDURE lake_list
GO
CREATE PROCEDURE lake_list
   ( @region varchar(30),
     @size integer,
     @lake_list_cursor CURSOR VARYING OUTPUT )
AS 
BEGIN
   DECLARE @ok SMALLINT
   EXECUTE check_authority @region, username, @ok OUTPUT
   IF @ok = 1
      BEGIN
      SET @lake_list_cursor = CURSOR LOCAL SCROLL FOR
         SELECT name, lat, long, size, boat_launch, cost
         FROM lake_inventory
         WHERE locale = @region AND area >= @size
         ORDER BY name
      OPEN @lake_list_cursor
      END
END
DECLARE @my_lakes_cursor CURSOR
DECLARE @my_region char(30)
SET @my_region = 'Northern Ontario'
EXECUTE lake_list @my_region, 500, @my_lakes_cursor OUTPUT
IF Cursor_Status('variable', '@my_lakes_cursor') <= 0
   BEGIN
   /* Some code to tell the user that there is no list of
   Lakes. */
   END
ELSE
   BEGIN
      FETCH @my_lakes_cursor INTO -- Destination here
      -- Continue with other code here.
END

See Also

Reference

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

Other Resources

Using Identifiers As Object Names

Help and Information

Getting SQL Server 2005 Assistance