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,
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,
.png)
