SQL Server Cursor Types - What is the Difference between LOCAL AND GLOBAL Cursor | SQL Server Tutorial / TSQL Tutorial

Local Cursor: 

The scope of Local Cursor is limited to the batch, stored procedure or trigger in which it is created. Once the Batch, Stored Procedure or Trigger is completed. The Local Cursor will not be available to use anymore.

GLOBAL CURSOR: 

The scope of GLOBAL Cursor is limited to the connection in which it is created. You can use GLOBAL CURSOR in multiple batches, you can open in first and fetch the data in second. You can also open the GLOBAL CURSOR in one Stored Procedure and Fetch the Data in Next Stored Procedure as long as they are using the same connection.

If you would not use the keyword Local or Global , the Cursor will be created with TYPE by using the Database Setting as shown below. 

Fig 1: Difference between Local Cursor and Global Cursor in SQL Server

Let's create a sample table and insert some records and do some test to prove our above definition.


--drop table dbo.Customer
Create table dbo.Customer ( 
CustomerId Int ,
CustomerName VARCHAR(100),
StreetAddress VARCHAr(100),
City VARCHAR(100),
State CHAR(2))
go

--Insert few Records in Sample Table
Insert into dbo.Customer
Select 1,'Aamir shahzad','Test Street Address','Charlotte','NC'
Union all
Select 2,'M Raza','Test Street Address','Charlotte','NC'
union all
Select 3,'John Smith','Test Street Address','New York City','NY'
union All
Select 4,'Christy Richard','Test Street Address','Rio Rancho','NM'




--Test with GLOBAL Cursor in Multiple Batches. 
use Test
go

DECLARE Customer_Cursor CURSOR 
--use LOCAL OR GLOBAL HERE
GLOBAL 
FOR
Select CustomerID,
CustomerName,
StreetAddress,
City,
State
from dbo.Customer
OPEN Customer_Cursor;
GO

--Terminate the Batch and change the Database 
use TestDB
go
FETCH NEXT FROM Customer_Cursor
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   FETCH NEXT FROM Customer_Cursor 
   END
CLOSE Customer_Cursor;
GO
DEALLOCATE Customer_Cursor;
GO

We will be able to see the records as we have defined Cursor as GLOBAL and it will be 
available during entire Connection , even we have terminated the first Batch by using GO
statement.
Fig 2: Global Cursor in SQL Server
--Test with LOCAL Cursor in Multiple Batches. use Test go DECLARE Customer_Cursor CURSOR --use LOCAL OR GLOBAL HERE LOCAL FOR Select CustomerID, CustomerName, StreetAddress, City, State from dbo.Customer OPEN Customer_Cursor; GO --Terminate the Batch and change the Database use TestDB go FETCH NEXT FROM Customer_Cursor WHILE (@@FETCH_STATUS <> -1) BEGIN FETCH NEXT FROM Customer_Cursor END CLOSE Customer_Cursor; GO DEALLOCATE Customer_Cursor; GO


As the scope for LOCAL Cursor is limited to Batch, Stored Procedure or Trigger, The second batch is not able to see the Cursor as we have defined LOCAL Cursor type in our above query
Fig 3: Local Cursor in SQL Server



Now let's perform the test with Stored Procedure and see how Local Cursor and Global Cursor works in Stored Procedure in SQL Server.

--Test with LOCAL Cursor in Multiple Batches. 
use Test
go

Create Procedure Dec_Cursor_Customer AS
BEGIN
DECLARE Customer_Cursor CURSOR 
--use LOCAL OR GLOBAL HERE
GLOBAL 
FOR
Select CustomerID,
CustomerName,
StreetAddress,
City,
State
from dbo.Customer
OPEN Customer_Cursor;
END

GO
Create Procedure Fetch_Cusor_Customer
AS 
BEGIN
FETCH NEXT FROM Customer_Cursor
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   FETCH NEXT FROM Customer_Cursor 
   END
END

--Execute the Procedures to What we get with GLOBAL and LOCAL Cursor Type

EXEC Dec_Cursor_Customer
GO
EXEC Fetch_Cusor_Customer
CLOSE Customer_Cursor;
GO
DEALLOCATE Customer_Cursor;
GO



If we execute the above Stored Procedure, we will get the results as we got in Fig 2. As we have declare as GLOBAL type ,we will be able to use it in multiple Stored Procedure as long as you run them in same connection. 


Go ahead and Alter the Stored Procedure and change the type from GLOBAL to Local and then execute the procedures. Even we are in same connection, We will get the error that we got in Fig 3. As the scope of Cursor is limited to Batch,Stored Procedure or Trigger once you define as LOCAL. 

Video Demo: To see the detailed Demo how Local Cursor and Global Cursor works, watch video.

No comments:

Post a Comment