DATABASE

SQL Server 2008 : Advanced Stored Procedure Programming and Optimization - Using Cursors in Stored Procedures

4/27/2011 6:19:09 PM
When using cursors in stored procedures in SQL Server, you need to be aware of the scope of the cursor and how it can be accessed within calling or called procedures. Cursors in SQL Server can be declared as local or global. A global cursor defined in a stored procedure is available until it is deallocated or when the connection closes. A local cursor goes out of scope when the stored procedure that declared it terminates or the procedure scope changes.

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 database option CURSOR_DEFAULT, which you set with the ALTER DATABASE statement. The default value for the option is_local_cursor_default is FALSE, which defaults cursors as global, to match the behavior of earlier versions of SQL Server. If this value in the sys.databases catalog view is set to TRUE, T-SQL cursors default to local cursors.

Tip

If neither GLOBAL nor LOCAL is specified, the default scope setting for cursors in SQL Server 2008 is determined by the Default to Local Cursor database option. This option currently defaults to OFF to provide backward compatibility with versions of SQL Server prior to 7.0, in which all cursors were global. This 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.


If stored procedures are nested, they can access cursors declared in higher-level stored procedures in the call tree, but only if the cursors are declared as global. If the cursor is declared as local, it can be referenced only within the scope of the stored procedure in which it is declared. It cannot be accessed from a called or calling procedure. If the cursor is declared as global, it can be accessed within the declaring procedure, in a called procedure, or even from outside the declaring procedure if the cursor is not deallocated before the procedure returns.

In the following example, procedure p1 creates a cursor defined as global that can then be accessed by procedure p2:

if object_id('p1') is not null
drop proc p1
go
if object_id('p2') is not null
drop proc p2
go
create proc p2
as
set nocount on
-- fetch from global cursor defined in calling proc p1
fetch c1
return
go

create proc p1
as
set nocount on
-- Declare global cursor
declare c1 cursor global for
select title_id, type from titles
open c1
fetch c1
exec p2
close c1
deallocate c1
go


exec p1
go


title_id type
-------- ------------
BI0194 biography

title_id type
-------- ------------
BI1408 biography



As you can see in the preceding example, the cursor c1 is defined as global in procedure p1 and can be accessed from within procedure p2.

Tip

To clean up the output when using cursors within stored procedures, specify the set nocount on option within the stored procedure to disable the n rows(s) affected that would normally be displayed after each invocation of the fetch statement.


Now, look what happens if you modify procedure p1 to declare the cursor as local:

alter proc p1
as
set nocount on
-- Declare local cursor
declare c1 cursor local for
select title_id, type from titles
open c1
fetch c1
exec p2
close c1
deallocate c1
go
exec p1
go

title_id type
-------- ------------
BI0194 biography

Msg 16916, Level 16, State 1, Procedure p2, Line 5
A cursor with the name 'c1' does not exist.

Notice in this example that the cursor c1 is not available to the procedure p2. The reason is that the cursor is defined as local and is accessible only within the scope of procedure p1. Because the cursor is localized to the scope of p1, you are able to define a cursor with the same name within the scope of procedure p2:

alter proc p2
as
set nocount on
-- Declare another local cursor with same name 'c1'
declare c1 cursor local for
select au_id, au_lname from authors
open c1
fetch c1
close c1
deallocate c1
return
go
exec p1
go

title_id type
-------- ------------
BI0194 biography

au_id au_lname
----------- ----------------------------------------
047-43-0360 Michener

Notice that when you define the scope of both cursors as local, each procedure can create a cursor with the same name without any conflict between them.

In addition to a global cursor defined in a calling procedure being available within a called procedure, the reverse is possible as well. A global cursor defined in a called procedure is available to the calling procedure as demonstrated in the following example:

alter proc p2
as
set nocount on
-- declare global cursor c2
declare c2 cursor global for
select au_id, au_lname from authors
open c2
--do not close/deallocate cursor so it can be used by calling proc p1
return
go
alter proc p1
as
set nocount on
declare c1 cursor local for
select title_id, type from titles
open c1
fetch c1
exec p2
-- fetch from global cursor declared in proc p2
fetch c2
close c1
deallocate c1
close c2
deallocate c2
return
go
exec p1
go


title_id type
-------- ----------
BI0194 biography

au_id au_lname
----------- ----------------------------------------
047-43-0360 Michener



As you can see in the preceding example, the global cursor defined in the called procedure p2 is available for use by the calling procedure p1 as long as the cursor is left open by the called procedure p2.

Note

Remember that global cursors persist beyond the scope of the procedure in which they are defined. If you are going to declare global cursors in called procedures to be accessed by the calling procedure, be sure the calling procedure closes and deallocates the cursor declared in the called procedure before it returns. Otherwise, the cursor will remain open and defined until the end of the user session. The following example demonstrates this behavior:

alter proc p1
as
set nocount on
declare c1 cursor local for
select title_id, type from titles
open c1
fetch c1
exec p2
-- fetch from global cursor declared in proc p2
fetch c2
close c1
deallocate c1
-- Cursor c2 is not closed/deallocated before return
return
go
exec p1
go
--Cursor c2 should still be open here so the following fetch will work
fetch c2
go


title_id type
-------- ----------
BI0194 biography


au_id au_lname
----------- ----------------------------------------
047-43-0360 Michener


au_id au_lname
----------- ----------------------------------------
052-04-3539 Gray




Using CURSOR Variables in Stored Procedures

Another method available in SQL Server 2008 for passing cursor result sets between stored procedures is using the cursor data type. The cursor data type can be used to bind a cursor result set to a local variable, and that variable can then be used to manage and access the cursor result set. Cursor variables can be referenced in any of the cursor management statements: OPEN, FETCH, CLOSE, and DEALLOCATE.

A stored procedure can pass cursor variables as output parameters only; cursor variables cannot be passed as input parameters. When defining a CURSOR output parameter, you must also specify the VARYING keyword.

When assigning a cursor to a cursor variable, you must use the SET command because an assignment select is not allowed. Cursor data types can either be the source or the target in a SET statement.

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

IF EXISTS ( SELECT * FROM sys.procedures
WHERE schema_id = schema_id('dbo')
AND name = N'cursor_proc')
DROP PROCEDURE dbo.cursor_proc
GO
create proc cursor_proc @cursor CURSOR VARYING OUTPUT
as
declare curs1 cursor global for
select cast(title as varchar(30)) as title , pubdate from titles
set @cursor = curs1
open curs1
return

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

Listing 1. Fetching Cursor Rows by 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
-------------------------------------------------------
Samuel Johnson 2008-09-19 00:00:00.000

title pubdate
-------------------------------------------------------
Freud, Dora, and Vienna 1900 2008-02-25 00:00:00.000

title pubdate
-------------------------------------------------------
Freud: A Life for Our Time 2008-06-21 00:00:00.000

title pubdate
-------------------------------------------------------
For Love of the World 2006-01-06 00:00:00.000

One of the problems with a cursor declared as a global cursor in the procedure is that you cannot invoke the procedure again within the same session unless the cursor is closed and deallocated. This can be a problem if you need to get the cursor into a cursor variable again. If you try to invoke the procedure again, and the cursor hasn’t been closed or deallocated, you get error messages, as shown in the following example:

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

Msg 16915, Level 16, State 1, Procedure cursor_proc, Line 4
A cursor with the name 'curs1' already exists.
Msg 16905, Level 16, State 1, Procedure cursor_proc, Line 6
The cursor is already open.


close curs1
deallocate curs1
go

One way to work around this issue is to use the CURSOR_STATUS function in the procedure to check whether the cursor exists yet before declaring it and also to check whether the cursor is already open before opening it. Thus, the stored procedure declares the cursor only if it doesn’t exist and opens the cursor only if it’s closed, but the stored procedure always returns the cursor in the cursor output parameter. Keeping this in mind, take a look at a revised version of the cursor_proc stored procedure:

IF EXISTS ( SELECT * FROM sys.procedures
WHERE schema_id = schema_id('dbo')
AND name = N'cursor_proc')
DROP PROCEDURE dbo.cursor_proc
GO
go
create proc cursor_proc @cursor CURSOR VARYING OUTPUT
as
if CURSOR_STATUS('global', 'curs1') = -3 -- cursor does not exist
declare curs1 cursor global for
select cast(title as varchar(30)) as title , pubdate from titles
if CURSOR_STATUS('global', 'curs1') = -1 -- cursor is not open
open curs1
set @cursor = curs1
return

When the procedure is written this way, you can now safely call the procedure at any time, even if the cursor is already open. If the cursor is open, it simply stores the cursor pointer into the cursor variable.

If you want to close the cursor, you can do so by using either the cursor variable or declared cursor name. When it is closed, however, you cannot fetch more rows from the cursor or cursor variable until it is reopened:

set nocount on
declare @curs CURSOR
exec cursor_proc @cursor = @curs output
fetch curs1
fetch @curs
-- close the cursor
close curs1
-- try to fetch from the cursor variable
fetch @curs
go

title pubdate
-------------------------------------------------------
Samuel Johnson 2008-09-19 00:00:00.000

title pubdate
-------------------------------------------------------
Freud, Dora, and Vienna 1900 2008-02-25 00:00:00.000

Msg 16917, Level 16, State 2, Line 7
Cursor is not open.

However, if the cursor has been assigned to a cursor variable, it cannot be fully deallocated until the last remaining reference to the cursor issues the DEALLOCATE command. Until all references to the cursor issue the DEALLOCATE command, the cursor can be reopened, but only by using the remaining cursor reference(s) that hasn’t issued the DEALLOCATE command. An example of this behavior is shown in Listing 2. If the cursor has not been closed, only the last deallocation of the cursor closes it.

Listing 2. 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 VARIABLE (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
-------------------------------------------------------
SAMUEL JOHNSON 2008-09-19 00:00:00.000


FETCH VIA VARIABLE:

TITLE PUBDATE
-------------------------------------------------------
FREUD, DORA, AND VIENNA 1900 2008-02-25 00:00:00.000
CLOSE BY NAME

DEALLOCATE BY NAME

ATTEMPT FETCH VIA VARIABLE (CURSOR SHOULD BE CLOSED):
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
-------------------------------------------------------
SAMUEL JOHNSON 2008-09-19 00:00:00.000


CLOSE AND DEALLOCATE VIA VARIABLE
ATTEMPT TO OPEN VIA VARIABLE (SHOULD FAIL, SINCE NOW FULLY DEALLOCATED):
MSG 16950, LEVEL 16, STATE 2, LINE 27
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 is accessible only through the cursor variable, as shown in Listing 3.

Listing 3. Assigning a Local Cursor to a Cursor Output Parameter
IF EXISTS ( SELECT * FROM sys.procedures
WHERE schema_id = schema_id('dbo')
AND name = N'cursor_proc2')
DROP PROCEDURE dbo.cursor_proc2
GO
create proc cursor_proc2 @cursor CURSOR varying output
as
declare curs1 cursor local for
select cast(title as varchar(30)) as 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:
Msg 16916, Level 16, State 1, Line 4
A cursor with the name 'curs1' does not exist.
ATTEMPT FETCH VIA VARIABLE:
title pubdate
-------------------------------------------------------
Samuel Johnson 2008-09-19 00:00:00.000


 
Other  
  •  SQL Server 2008 : T-SQL Stored Procedure Coding Guidelines
  •  SQL Server 2008 : Leveraging the Microsoft Sync Framework
  •  SQL Server 2008 : Using ADO.NET Data Services
  •  SQL Server 2008 : Developing with LINQ to SQL (part 2) - Uncovering LINQ to SQL with Linqpad
  •  SQL Server 2008 : Developing with LINQ to SQL (part 1)
  •  Getting Comfortable with ADO.NET 3.5 and SQL Server 2008
  •  SQL Server System and Database Administration : System Views
  •  SQL Server System and Database Administration : System Tables & System Stored Procedures
  •  SQL Server System and Database Administration : System Databases
  •  SQL Server 2008 : Monitoring Your Server - Monitoring Your CPU
  •  Programming Microsoft SQL Server 2005 : Querying XML Data Using XQuery (part 3) - XML DML & Converting a Column to XML
  •  Programming Microsoft SQL Server 2005 : Querying XML Data Using XQuery (part 2) - SQL Server 2005 XQuery in Action
  •  Programming Microsoft SQL Server 2005 : Querying XML Data Using XQuery (part 1) - XQuery Defined
  •  SQL Server 2008 : Monitoring Your Server - Familiarizing Yourself with the Performance Monitor
  •  Programming Microsoft SQL Server 2005 : FOR XML Commands (part 3) - OPENXML Enhancements in SQL Server 2005
  •  Programming Microsoft SQL Server 2005 : FOR XML Commands (part 2) - FOR XML EXPLICIT
  •  Programming Microsoft SQL Server 2005 : FOR XML Commands (part 1) - FOR XML RAW & FOR XML AUTO
  •  SQL Server 2008 : Audit-Related Startup and Shutdown Problems
  •  SQL Server 2008 : Creating SQL Server Audits Using the GUI
  •  SQL Server 2008 : Creating Database Audit Specifications
  •  
    Video
    Top 10
    Beginning Android 3 : The Input Method Framework - Fitting In
    Windows 7 : Creating and Managing Groups
    Windows 7 : Managing User Properties
    Exchange Server 2010 : Unique database names
    Exchange Server 2010 : Transaction log replay: The foundation for DAG replication
    Exchange Server 2010 : Active Manager - Automatic database transitions & Best copy selection
    Exchange Server 2010 : Breaking the link between database and server
    iPhone 3D Programming : Drawing an FPS Counter (part 2) - Rendering the FPS Text
    iPhone 3D Programming : Drawing an FPS Counter (part 1) - Generating a Glyphs Texture with Python
    Mobile Application Security : Mobile Geolocation - Geolocation Methods & Geolocation Implementation
    Most View
    Microsoft XNA Game Studio 3.0 : Displaying Images - Using Resources in a Game (part 4) - Filling the Screen
    The Language of Apple Platforms : Object-Oriented Programming and Objective-C
    SharePoint 2010 : Implementing Authentication Scenarios
    sp_configure and SQL Server Management Studio
    Windows 7 : Networking with TCP/IP (part 1) - Understanding IPv4 & Using Private IPv4 Addresses and Networking Protocols
    Exchange Server 2010 server roles (part 2)
    Sync Your iPad with iTunes : Manually Transferring Music, Movies, Podcasts, and More on Your iPad (Drag-and-Drop Method)
    IIS 7.0 : Managing Configuration - Delegating Configuration (part 1)
    Windows 7 : Scheduling Maintenance Tasks
    Creating and Managing Views in SQL Server 2008 : Creating Views
    Windows 7 :Navigating Your Computer with the Address Bar (part 1) - Accessing Locations on Your Computer
    Sharepoint 2007: Approve or Reject a File or List Item
    Mobile Application Security : SMS Security - Protocol Attacks (part 1)
    Building Android Apps: Web SQL Database (part 1) - Creating a Database
    Using Shapes in XAML
    Programming .NET Security : Digital Signatures Explained
    Exchange Server 2010 : Track Exchange Performance (part 1) - Use the Performance Tools Available
    Download Web Content via HTTP
    jQuery 1.3 : Headline rotator
    Windows Server 2008 : Designing Organizational Unit and Group Structure - Group Policies and OU Design