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.
CURSOR_STATUS
(
{'local', 'cursor_name'}
| {'global', 'cursor_name'}
| {'variable', 'cursor_variable'}
)
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. | ||
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
| Data Types | Cursor Functions |
| Using Identifiers |