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.
.gif "Topic link icon")
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