Microsoft® SQL Server 2008 R2 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 44.9).

Listing 44.9 Checking @@NESTLEVEL in Nested Stored Procedures

Although ...