@@NESTLEVEL (Transact-SQL)

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

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

👁 Image
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

Create a Stored Procedure
@@TRANCOUNT (Transact-SQL)