Microsoft SQL Server 2008 R2 Unleashed- P181: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 | 1754 CHAPTER 44 Advanced Stored Procedure Programming and Optimization LISTING Checking @@NESTLEVEL in Nested Stored Procedures create proc main_proc as print Nesting Level in main_proc before sub_proc1 1 str @@NESTLEVEL 1 exec sub_proc1 print Nesting Level in main_ proc after sub_proc1 str @@NESTLEVEL 1 exec sub_proc2 print Nesting Level in main_proc after sub_proc2 str @@NESTLEVEL 1 return go create proc sub_proc1 as print Nesting Level in sub_proc1 before sub_proc2 str @@NESTLEVEL 1 exec sub_proc2 print Nesting Level in sub_proc1 after sub_proc2 str @@NESTLEVEL 1 return go create proc sub_proc2 as print Nesting Level in sub_proc2 str @@NESTLEVEL 1 return go print Nesting Level before main_proc str @@NESTLEVEL 1 exec main_proc print Nesting Level after main_proc str @@NESTLEVEL 1 go The module main_proc depends on the missing object sub_proc1 . The module will still be created however it cannot run successfully until the object exists. The module main_proc depends on the missing object sub_proc2 . The module will still be created however it cannot run successfully until the object exists. The module sub_proc1 depends on the missing object sub_proc2 . The module will still be created however it cannot run successfully until the object exists. Nesting Level before main_proc 0 Nesting Level in main_proc before sub_proc1 1 Nesting Level in sub_proc1 before sub_proc2 2 Nesting Level in sub_proc2 3 Nesting Level in sub_proc1 after sub_proc2 2 Nested Stored Procedures 1755 Nesting Level in main_proc after sub_proc1 1 Nesting Level in sub_proc2 2 Nesting Level in main_proc after sub_proc2 1 Nesting Level after main_proc 0 Although a limit exists for the number of levels to which procedures can be nested the number of stored procedures that can be called from within a single procedure is limitless. The main-level procedure can call potentially hundreds of other procedures. As long as the other procedures never invoke another procedure the nesting level never exceeds
