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:
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
Hi Siva,
It is "Code Snippet" available in Windows Live Writer add-ins.
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
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.
Post a Comment