How do you determine the maximum nested-level of Stored Procedure ?

@@NESTLEVEL Returns the nesting level of the current stored procedure execution (initially 0) on the local server. 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. 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

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

The current nested level can be determine by : @@NESTLEVEL, The maximum nested level is 32 . ie: 1. Creating stored procedure : CREATE PROC PROC_SAMPLE1 AS BEGIN PRINT @@NESTLEVEL EXEC PROC_SAMPLE1 END 2. Executing stored procedure : EXEC PROC_SAMPLE1 3. Result : 1 2 3 .. .. 32 Msg 217, Level 16, State 1, Procedure PROC_SAMPLE1, Line 5 Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).