Cursors in Stored Procedures

Cursors in SQL Server 2000 can be declared as local or global. A global cursor defined in a stored procedure is available until deallocated or when the connection closes. A local cursor goes out of scope when the stored procedure that declared it terminates. Only stored procedures called from within the procedure that declared the cursor can reference a higher-level local cursor. If neither the GLOBAL nor LOCAL option is specified when the cursor is declared in a stored procedure, the default cursor type is determined by the current setting of the database level option, default to local cursor. In SQL Server 2000, the default value for this database option is FALSE, meaning all cursors will be global by default.

As Figure 28.6 illustrates, if stored procedures are nested, they can access cursors declared in higher-level stored procedures in the call tree, whether the cursors are declared as global or local.

Using CURSOR Variables in Stored Procedures

SQL Server 2000 allows you to declare variables or parameters with a cursor datatype. You must use the SET command to assign a value to a cursor variable because an assignment select is not allowed. Cursor datatypes can be the source or the target in a SET statement. A stored procedure can pass a cursor variable as an output parameter only?cursor variables cannot be passed as input parameters. Cursor variables can be referenced in any of the cursor management statements: OPEN, FETCH, CLOSE, and DEALLOCATE. When defining a CURSOR output parameter, the VARYING keyword must also be specified.

The following stored procedure declares a cursor, opens it, and passes it back as an output parameter:

create proc cursor_proc @cursor CURSOR VARYING OUTPUT 
as
declare curs1 cursor for select title, pubdate from titles
set @cursor = curs1
open curs1

A cursor variable and the declared cursor name can be used interchangeably. You can use either the variable name or the declared name to open, fetch, close, and deallocate the cursor. Fetching using either the cursor name or the cursor variable will fetch the next row in the cursor resultset. Listing 28.16 illustrates how each fetch gets the next row in the resultset.

Listing 28.16 Fetching Cursor Rows Using the Declared Cursor Name and a Cursor Variable

set nocount on
declare @curs CURSOR
exec cursor_proc @cursor = @curs output

fetch curs1
fetch @curs
fetch curs1
fetch @curs
go

title                                                  pubdate
----------------------------------------------------- ------------------------
The Busy Executive's Database Guide                    1991-06-12 00:00:00.000

title                                                  pubdate
----------------------------------------------------- -------------------------
Cooking with Computers: Surreptitious Balance Sheets   1991-06-09 00:00:00.000

title                                                  pubdate
----------------------------------------------------- -------------------------
You Can Combat Computer Stress!                        1991-06-30 00:00:00.000

title                                                  pubdate
------------------------------------------------------ ------------------------
Straight Talk About Computers                          1991-06-22 00:00:00.000

If the cursor is closed using either the cursor variable or the declared cursor name, you cannot fetch more rows from the cursor until it is reopened:

declare @curs CURSOR 
exec cursor_proc @cursor = @curs output
fetch curs1
fetch @curs
close curs1
fetch @curs
go

title                                                   pubdate
-------------------------------------------------------------------------------
The Busy Executive's Database Guide                     1991-06-12 00:00:00.000

title                                                   pubdate
------------------------------------------------------- -----------------------
Cooking with Computers: Surreptitious Balance Sheets    1991-06-09 00:00:00.000

Server: Msg 16917, Level 16, State 2, Line 9
Cursor is not open.

However, if the cursor is deallocated using either the cursor variable or the cursor name, the cursor definition still exists until it is deallocated via the last remaining reference to the cursor. The cursor can be reopened, but only by using the remaining cursor reference(s) as shown in Listing 28.17. If the cursor has not been closed, only the last deallocation of the cursor closes it.

Listing 28.17 Deallocating a Cursor by Cursor Name and Cursor Variable

declare @curs CURSOR
exec cursor_proc @cursor = @curs output

print 'FETCH VIA NAME:'
fetch curs1

print 'FETCH VIA VARIABLE:'
fetch @curs

print 'CLOSE BY NAME'
close curs1
print 'DEALLOCATE BY NAME'
deallocate curs1

print 'ATTEMPT FETCH VIA VARABLE (CURSOR SHOULD BE CLOSED):'
fetch @curs

print 'ATTEMPT TO OPEN VIA VARIABLE (CURSOR SHOULD OPEN, NOT DEALLOCATED YET)'
open @curs

print 'ATTEMPT FETCH VIA VARIABLE (SHOULD START FROM BEGINNING AGAIN):'
fetch @curs

print 'CLOSE AND DEALLOCATE VIA VARIABLE'
close @curs
deallocate @curs

print 'ATTEMPT TO OPEN VIA VARIABLE (SHOULD FAIL, SINCE NOW FULLY DEALLOCATED):'
open @curs
go

FETCH VIA NAME:
title                                                pubdate
---------------------------------------------------- -----------------------
The Busy Executive's Database Guide                  1991-06-12 00:00:00.000

FETCH VIA VARABLE:
title                                                pubdate
---------------------------------------------------- -----------------------
Cooking with Computers: Surreptitious Balance Sheets 1991-06-09 00:00:00.000

CLOSE BY NAME

DEALLOCATE BY NAME

ATTEMPT FETCH VIA VARIABLE (CURSOR SHOULD BE CLOSED):
Server: Msg 16917, Level 16, State 2, Line 15
Cursor is not open.
ATTEMPT TO OPEN VIA VARIABLE (CURSOR SHOULD OPEN, NOT DEALLOCATED YET)

ATTEMPT FETCH VIA VARIABLE (SHOULD START FROM BEGINNING AGAIN):
title                                                pubdate
---------------------------------------------------- -----------------------
The Busy Executive's Database Guide                  1991-06-12 00:00:00.000

CLOSE AND DEALLOCATE VIA VARIABLE

ATTEMPT TO OPEN VIA VARIABLE (SHOULD FAIL, SINCE NOW FULLY DEALLOCATED):
Server: Msg 16950, Level 16, State 2, Line 28
The variable '@curs' does not currently have a cursor allocated to it.

If the cursor is declared as a local cursor within a stored procedure, it can still be passed back in an output variable to a cursor variable, but it will only be accessible through the cursor variable, as shown in Listing 28.18.

Listing 28.18 Assigning a Local Cursor to a Cursor Output Parameter

create proc cursor_proc2 @cursor CURSOR varying output
as
declare curs1 cursor local for select title, pubdate from titles
set @cursor = curs1
open curs1
go
declare @curs CURSOR
exec cursor_proc2 @cursor = @curs output

print 'ATTEMPT FETCH VIA NAME:'
fetch next from curs1
print 'ATTEMPT FETCH VIA VARIABLE:'
fetch next from @curs
go

ATTEMPT FETCH VIA NAME:
Server: Msg 16916, Level 16, State 1, Line 5
A cursor with the name 'curs1' does not exist.
ATTEMPT FETCH VIA VARIABLE:
title                                                  pubdate
------------------------------------------------------ -----------------------
The Busy Executive's Database Guide                    1991-06-12 00:00:00.000