Cursors in Stored Procedures

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.

TIP

The default setting in SQL Server 2000 for all cursors to be global if neither GLOBAL or LOCAL is specified provides backward compatibility for versions of SQL Server prior to 7.0, in which all cursors were global. The default setting might change in future versions, so it is recommended that you explicitly specify the local or global option when declaring your cursors so your code will not be affected by changes to the default setting.

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.

Figure 28.6. If stored procedures are nested, they can access cursors declared in higher-level stored procedures in the call tree.

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