Microsoft SQL Server 2008 R2 Unleashed- P180:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1744 CHAPTER 44 Advanced Stored Procedure Programming and Optimization If stored procedures are nested they can access cursors declared in higher-level stored procedures in the call tree but only if the cursors are declared as global. If the cursor is declared as local it can be referenced only within the scope of the stored procedure in which it is declared. It cannot be accessed from a called or calling procedure. If the cursor is declared as global it can be accessed within the declaring procedure in a called procedure or even from outside the declaring procedure if the cursor is not deallocated before the procedure returns. In the following example procedure pl creates a cursor defined as global that can then be accessed by procedure p2 if object_id p1l is not null drop proc pl go if object_id p2l is not null drop proc p2 go create proc p2 as set nocount on -- fetch from global cursor defined in calling proc pl fetch cl return go create proc p1 as set nocount on -- Declare global cursor declare c1 cursor global for select title_id type from titles open c1 fetch c1 exec p2 close c1 deallocate c1 go exec pl go title_id type BI0194 biography Using Cursors in Stored Procedures 1745 title_id type . BI1408 biography As you can see in the preceding example the cursor cl is defined as global in procedure pl and can be accessed from within procedure p2. TIP To clean up the output when using cursors within stored procedures specify the set nocount on option within the stored procedure to disable the n rows s affected that would normally be displayed after each invocation of the fetch statement. Now look what happens if you modify procedure p1 to declare the cursor as local alter proc pl as set nocount on -- Declare local cursor declare c1 cursor local for select title_id type from titles open c1 fetch cl exec p2 close c1 deallocate c1 go exec p1 go title_id type 44 BI0194 biography Msg 16916 Level 16 State 1 Procedure p2 Line 5 A cursor with the name c1 does not exist. .
