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.