Microsoft® SQL Server 2012 Unleashed

Nested Stored Procedures

Stored procedures can call other stored procedures, and any of those procedures can call other procedures, up to a maximum nesting level of 32 levels deep. If you exceed the 32-level nesting limit, an error message is raised, the batch is aborted, and any open transaction in the session is rolled back. The nesting level limit prevents a recursive procedure from calling itself repeatedly in an infinite loop until a stack overflow occurs. To check the depth to which a procedure is nested, you use the system function @@NESTLEVEL (see Listing 46.10).

LISTING 46.10 Checking @@NESTLEVEL in Nested Stored Procedures

Click here to view code image

create proc main_procasprint 'Nesting Level in main_proc before sub_proc1 = ' + ...