@@NESTLEVEL (Transact-SQL)

@@NESTLEVEL (Transact-SQL)

 

THIS TOPIC APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

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

Topic link icon Transact-SQL Syntax Conventions


@@NESTLEVEL

int      

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.

The following example creates two procedures: one that calls the other, and one that displays the @@NESTLEVEL setting of each.

USE AdventureWorks2012;
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

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)

Community Additions

ADD
Show:
© 2016 Microsoft