Microsoft® SQL Server™ 2000 Unleashed, Second Edition

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 limit, an error message will be raised, the batch will be aborted, and any open transaction in the session will be 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 that a procedure is nested, use the system function @@NESTLEVEL (see Listing 28.19).

Listing 28.19. Checking @@NESTLEVEL in Nested Stored Procedures

create proc main_proc as print 'Nesting Level in main_proc before sub_proc1 = ' + str(@@NESTLEVEL, 1) exec sub_proc1 ...