Guru's Guide to SQL Server™ Stored Procedures, XML, and HTML, The

Nesting

You can nest stored procedure calls up to 32 levels deep. Use the @@NESTLEVEL automatic variable to check the nesting level from within a stored procedure or trigger. From a command batch, @@NESTLEVEL returns 0. From a stored procedure called from a command batch and from first-level triggers, @@NESTLEVEL returns 1. From a procedure or trigger called from nesting level 1, @@NESTLEVEL returns 2; procedures called from level 2 procedures return level 3, and so on. Objects (including temporary tables) and cursors created within a stored procedure are visible to all objects it calls. Objects and cursors created in a command batch are visible to all the objects referenced in the command batch.