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 pubs
GO
CREATE PROCEDURE OpenCrsr AS
DECLARE SampleCrsr CURSOR FOR
SELECT au_lname
FROM authors
WHERE au_lname 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
SQL Server 7.0 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.
Local cursors offer important protection for cursors implemented in stored procedures and triggers. Because global cursors can be referenced outside the stored procedure or trigger in which they are declared, 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.
Also, 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 default to local cursor controls the default taken by the DECLARE CURSOR statement if neither LOCAL nor GLOBAL is specified. If default to local cursor is true, Transact-SQL cursors default to local. If the option is false, Transact-SQL cursors default to global. In SQL Server 7.0, the default to local cursors option itself defaults to FALSE 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 pubs
GO
/* Create a procedure with a cursor output parameter. */
CREATE PROCEDURE OpenCrsr @OutCrsr CURSOR VARYING OUTPUT AS
SET @OutCrsr = CURSOR FOR
SELECT au_lname
FROM authors
WHERE au_lname 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.