@@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)
प्रतिक्रिया
https://aka.ms/ContentUserFeedback.
जल्द आ रहा है: 2024 के दौरान हम सामग्री के लिए फीडबैक तंत्र के रूप में GitHub मुद्दों को चरणबद्ध तरीके से समाप्त कर देंगे और इसे एक नई फीडबैक प्रणाली से बदल देंगे. अधिक जानकारी के लिए, देखें:के लिए प्रतिक्रिया सबमिट करें और देखें