Saturday, April 30, 2011

Database Option: CURSOR DEFAULT {LOCAL | GLOBAL} - Is CURSOR Global?

Setting the value of this option as GLOBAL has been understood wrongly by many developers. All you have to remember is, this “global” does not refer all connections, it refers the current connection. If either database option or at the declaration of cursor, GLOBAL is set, it means that scope of the cursor is global to the connection. Simply it can be referenced in any stored procedure or batch executed by same connection. If LOCAL is used, it is only for the batch which it was created. See the code below;

-- Parent SP with a cursor
CREATE PROC TestCursorParentSP
AS
BEGIN
 
    DECLARE @SalesOrderID int
    
    DECLARE cur CURSOR GLOBAL
    FOR
    SELECT SalesOrderID 
    FROM Sales.SalesOrderHeader 
    
    OPEN cur
    FETCH NEXT FROM cur INTO @SalesOrderID
    
    SELECT @SalesOrderID
    
    EXEC TestCursorChildSP
    
    CLOSE cur
    DEALLOCATE cur
END
 
-- Child SP that accesses cursor declared by parent
CREATE PROC TestCursorChildSP
AS
BEGIN
 
    DECLARE @SalesOrderID int
    
    FETCH NEXT FROM cur INTO @SalesOrderID
    
    SELECT @SalesOrderID
    
END
 
-- Executing parent sp
-- Get values from both SPs
EXEC TestCursorParentSP

If the cursor is declared as LOCAL, child SP will not be able to access it.

4 comments:

Sivaprasad said...

Hi Dinesh,

Thanks for the nice post.

Let me know how do you post the SQL Server Code in a scroll window in blogger site.

Thanks, Sivaprasad.S

Dinesh Priyankara said...

Hi Siva,

It is "Code Snippet" available in Windows Live Writer add-ins.

Adhi said...

Hi Dinesh, Question on this, where in SQL Instance settings do I find the default cursor scope. I wanted to check what was is set to. I'm using SS2008

Dinesh Priyankara said...

Hi Adhi,

Default Cursor is set at the database level. If you get the properties of your database (right click on the database in management studio) and go to "Options", then you will the property with the default value.