Local and Global Cursors in SQL Server

Based on the scope, Cursors can be classified into two types in SQL Server, namely Local Cursors and Global Cursors. They behave just like Local and Global Temporary Tables with some differences. In this post, I'll write about how they work.

Just like temporary tables, the scope of a cursor can be Local or Global. Global Cursors can be accessed anywhere in the program once they are declared whereas Local Cursors can be accessed only in the respective batch. There is an option to set whether the default cursor in a particular database is Global or Local. By default it is set to Global. To know that, run the following code,

SELECT is_local_cursor_default FROM sys.databases WHERE name = DB_NAME()

If this query returns 0 then the default cursor is Global. If it is 1 then it means the default cursor of the database is Local. The scope of the default cursor can be changed to LOCAL with the following ALTER statement,

ALTER DATABASE AdventureWorks2012 SET CURSOR_DEFAULT LOCAL

If Global Cursor is declared inside a stored procedure or a trigger, it can be accessed from another Stored Procedure or a Trigger whereas Local Cursor cannot be accessed like this. Global Cursors should be manually closed and de-allocated or it will be de-allocated on closing the connection whereas Local Cursors are automatically closed and de-allocated as soon as the query analyzer crosses its batch.

Global Cursor and Local Cursor have different namespaces, so both can be declared with the same name. Let's see how this works,

Assuming the default scope of cursor is set to GLOBAL, let's create one as follows,

USE AdventureWorks2012

GO

DECLARE MYCURSOR CURSOR

FOR

SELECT * FROM Production.Product

Now cursor is declared for table Product. Now it can be opened in an independent SQL Statement,

OPEN MYCURSOR

If any row is to be fetched then it can also be done with an independent statement,

FETCH NEXT FROM MYCURSOR

WHILE @@FETCH_STATUS = 0

BEGIN

   FETCH NEXT FROM MYCURSOR

END

This cursor can be closed and de-allocated independently too,

CLOSE MYCURSOR

DEALLOCATE MYCURSOR

The above case is not with Local Cursor. If a local cursor has to be created on the same table then it goes as follows,

DECLARE MYCURSOR CURSOR LOCAL

FOR

SELECT * FROM Production.Document

OPEN MYCURSOR

FETCH NEXT FROM MYCURSOR

WHILE @@FETCH_STATUS = 0

BEGIN

    FETCH NEXT FROM MYCURSOR

END

CLOSE MYCURSOR

DEALLOCATE MYCURSOR

The above SQL code should be executed in a single batch because of the keyword LOCAL which means the cursor is a local scoped one. If the default scope of cursor is set to LOCAL then a keyword GLOBAL should be used while declaring a global cursor. The above two cursors can be declared in a single connection.

There is a scenario where a Global Cursor can be declared inside a Stored Procedure or a Trigger and can be accessed from outside the procedure or trigger. To know that functionality, let's create a Stored Procedure as follows,

CREATE PROCEDURE GLOBAL_CURSOR_TEST

AS

BEGIN

DECLARE MYCURSOR CURSOR

FOR

SELECT * FROM Production.Product

END

Now execute the Stored Procedure which declares the cursor,

EXEC GLOBAL_CURSOR_TEST

Now this cursor can be accessed from either outside this procedure independently or inside another Stored Procedure. For now let's try independently,

OPEN MYCURSOR

FETCH NEXT FROM MYCURSOR

WHILE @@FETCH_STATUS = 0

BEGIN

    FETCH NEXT FROM MYCURSOR

END

CLOSE MYCURSOR

DEALLOCATE MYCURSOR

Now you can observe that this fetches the result from the table Production.Product with a cursor MYCURSOR. This cannot be done when a Local Stored Procedure is created within the Stored Procedure.

Here is the link for the official documentation about scope of cursors, by Microsoft,

http://technet.microsoft.com/en-us/library/ms189238(v=sql.105).aspx