SQL Server/T-SQL Tutorial/System Settings/NESTLEVEL

Displaying the Nesting Level for the Current Stored Procedure Context

5> -- First procedure
6> CREATE PROCEDURE usp_QuickAndDirty
7> AS
8> SELECT @@NESTLEVEL
9> GO
1>
2> -- Second procedure
3> CREATE PROCEDURE usp_Call_QuickAndDirty
4> AS
5> SELECT @@NESTLEVEL
6> EXEC usp_QuickAndDirty
7> GO
1>
2> -- Returns 0 nest level
3> SELECT @@NESTLEVEL
4> -- Returns 1 and 2 nest level
5> EXEC usp_Call_QuickAndDirty
6>
7> drop procedure usp_QuickAndDirty;
8> go
-----------
          0
(1 rows affected)
-----------
          1
(1 rows affected)
-----------
          2
(1 rows affected)
1> drop procedure usp_Call_QuickAndDirty
2> GO