@@NESTLEVEL (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

Returns the nesting level of the current stored procedure execution (initially 0) on the local server.

Transact-SQL syntax conventions

Syntax

@@NESTLEVEL  

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's 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's 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)