O'Reilly logo

Microsoft® SQL Server 2012 Unleashed by Alex T. Silverstein, Chris Gallelli, Paul T. Bertucci, Ray Rankins

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required