Scope of Transact-SQL Cursor Names
Microsoft SQL Server 2005 supports the GLOBAL and LOCAL keywords on the DECLARE CURSOR statement to define the scope of the cursor name. GLOBAL specifies that the cursor name is global to the connection. LOCAL specifies that the cursor name is LOCAL to the stored procedure, trigger, or batch containing the DECLARE CURSOR statement.
Prior to Microsoft SQL Server version 7.0, the names of Transact-SQL cursors were global to the connection. You could execute one stored procedure that creates a cursor, and then call another stored procedure that fetches the rows from that cursor:
USE AdventureWorks;
GO
CREATE PROCEDURE OpenCrsr AS
DECLARE SampleCrsr CURSOR FOR
SELECT TOP (20)LastName
FROM Person.Contact
WHERE LastName LIKE 'S%';
OPEN SampleCrsr;
GO
CREATE PROCEDURE ReadCrsr AS
FETCH NEXT FROM SampleCrsr
WHILE (@@FETCH_STATUS <> -1)
BEGIN
FETCH NEXT FROM SampleCrsr
END
GO
EXEC OpenCrsr; /* DECLARES and OPENS SampleCrsr. */
GO
EXEC ReadCrsr; /* Fetches the rows from SampleCrsr. */
GO
CLOSE SampleCrsr;
GO
DEALLOCATE SampleCrsr;
GO
Local cursors offer important protection for cursors implemented in stored procedures and triggers. Global cursors can be referenced outside the stored procedure or trigger in which they are declared. Consequently, they can be inadvertently changed by statements outside the stored procedure or trigger. Local cursors are more secure than global cursors because they cannot be referenced outside a stored procedure, unless deliberately passed back to the caller as a cursor output parameter.
Because global cursors can be referenced outside a stored procedure or trigger, they can have unintended side effects that influence other statements. An example is a stored procedure that creates a global cursor with a name of xyz and leaves the cursor open when it completes. An attempt to declare another global cursor with the name xyz after the stored procedure completed fails with a duplicate name error.
Global and local cursors have separate name spaces, so it is possible to have both a global cursor and a local cursor with the same name at the same time. The Transact-SQL statements that accept a cursor name parameter also support the GLOBAL keyword to identify the scope of the name. If GLOBAL is not specified, and there are both a local and global cursor with the name specified in the cursor name parameter, the local cursor is referenced.
The database option CURSOR_DEFAULT, set with the ALTER DATABASE statement, controls the default taken by the DECLARE CURSOR statement if neither LOCAL nor GLOBAL is specified. The current value for this database option is stored in the sys.databases catalog view. If the value in the column is local_cursor_default in the sys.databases catalog view is true, Transact-SQL cursors default to local. If the option is false, Transact-SQL cursors default to global. In SQL Server 2005, the database option itself defaults to FALSE (GLOBAL) to match the behavior of earlier versions of SQL Server.
Stored procedures that DECLARE and OPEN local cursors can pass the cursors out for use by the calling stored procedure, trigger, or batch. This is done using an OUTPUT parameter defined with the new CURSOR VARYING data type. Cursor variables can only be used as OUTPUT parameters. They cannot be used for input parameters. The cursor must be open when the stored procedure completes to be passed back in an OUTPUT parameter. Local variables can also be declared with the new CURSOR data type to hold a reference to a local cursor.
USE AdventureWorks;
GO
/* Create a procedure with a cursor output parameter. */
CREATE PROCEDURE OpenCrsr @OutCrsr CURSOR VARYING OUTPUT AS
SET @OutCrsr = CURSOR FOR
SELECT TOP (20) LastName
FROM Person.Contact
WHERE LastName LIKE 'S%';
OPEN @OutCrsr;
GO
/* Allocate a cursor variable. */
DECLARE @CrsrVar CURSOR;
/* Execute the procedure created earlier to fill
the variable. */
EXEC OpenCrsr @OutCrsr = @CrsrVar OUTPUT;
/* Use the variable to fetch the rows from the cursor. */
FETCH NEXT FROM @CrsrVar
WHILE (@@FETCH_STATUS <> -1)
BEGIN
FETCH NEXT FROM @CrsrVar
END;
CLOSE @CrsrVar;
DEALLOCATE @CrsrVar;
GO
The database APIs do not support cursor output parameters on stored procedures. A stored procedure that contains a cursor output parameter cannot be executed directly from a database API function. These stored procedures can only be executed from another stored procedure, a trigger, or a Transact-SQL batch or script.
A GLOBAL cursor is available until it is explicitly deallocated or the connection is closed. LOCAL cursors are implicitly deallocated when the stored procedure, trigger, or batch in which they were created terminates, unless the cursor has been passed back as a parameter. The LOCAL cursor will then be implicitly deallocated when the parameter or variable referencing the cursor in the code that called the procedure goes out scope.
See Also
Other Resources
ALTER DATABASE (Transact-SQL)
DECLARE CURSOR (Transact-SQL)