CURSOR_STATUS
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.
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 and:
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 and:
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.
USE pubs
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 for him/her */
END
ELSE
BEGIN
FETCH @my_lakes_cursor INTO -- Destination here
-- Continue with other code here.
END