Displaying the Nesting Level for the Current Stored Procedure Context : NESTLEVEL « System Settings « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. System Settings
3. NESTLEVEL

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