@@NESTLEVEL (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Returns the nesting level of the current stored procedure execution (initially 0) on the local server.
Transact-SQL syntax conventions
Syntax
@@NESTLEVEL
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Return Types
int
Remarks
Each time a stored procedure calls another stored procedure or executes managed code by referencing a common language runtime (CLR) routine, type, or aggregate, the nesting level is incremented. When the maximum of 32 is exceeded, the transaction is terminated.
When @@NESTLEVEL is executed within a Transact-SQL string, the value returned is 1 + the current nesting level. When @@NESTLEVEL is executed dynamically by using sp_executesql the value returned is 2 + the current nesting level.
Examples
A. Using @@NESTLEVEL in a procedure
The following example creates two procedures: one that calls the other, and one that displays the @@NESTLEVEL
setting of each.
USE AdventureWorks2022;
GO
IF OBJECT_ID (N'usp_OuterProc', N'P')IS NOT NULL
DROP PROCEDURE usp_OuterProc;
GO
IF OBJECT_ID (N'usp_InnerProc', N'P')IS NOT NULL
DROP PROCEDURE usp_InnerProc;
GO
CREATE PROCEDURE usp_InnerProc AS
SELECT @@NESTLEVEL AS 'Inner Level';
GO
CREATE PROCEDURE usp_OuterProc AS
SELECT @@NESTLEVEL AS 'Outer Level';
EXEC usp_InnerProc;
GO
EXECUTE usp_OuterProc;
GO
Here is the result set.
Outer Level
-----------
1
Inner Level
-----------
2
B. Calling @@NESTLEVEL
The following example shows the difference in values returned by SELECT
, EXEC
, and sp_executesql
when each of them calls @@NESTLEVEL
.
CREATE PROC usp_NestLevelValues AS
SELECT @@NESTLEVEL AS 'Current Nest Level';
EXEC ('SELECT @@NESTLEVEL AS OneGreater');
EXEC sp_executesql N'SELECT @@NESTLEVEL as TwoGreater' ;
GO
EXEC usp_NestLevelValues;
GO
Here is the result set.
Current Nest Level
------------------
1
(1 row(s) affected)
OneGreater
-----------
2
(1 row(s) affected)
TwoGreater
-----------
3
(1 row(s) affected)
See Also
Configuration Functions (Transact-SQL)
Create a Stored Procedure
@@TRANCOUNT (Transact-SQL)