|
|
Error Message
Server: Msg 217, Level 16, State 1, Line 1
Maximum stored procedure, function, trigger, or view
nesting level exceeded (limit 32).
Causes
Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routing, type or aggregate. Similarly, user-defined functions can also be nested, that is, one user-defined function can call another. In both cases, the maximum level of nesting is up to 32 levels.
|
The nesting level increases by one when the called stored procedure, managed code reference, or user-defined function begins execution and decreases by one when the called stored procedure, managed code reference, or user-defined function completes execution. Attempting to exceed the maximum of 32 levels of nesting causes the whole calling chain to fail and for this error message to be raised.
To illustrate how this error message can be encountered, here’s a recursive user-defined function that performs a simple summation of the integer values from 1 to the input parameter:
CREATE FUNCTION [dbo].[ufn_Summation] ( @Input INT )
RETURNS INT
AS
BEGIN
IF @Input = 1
RETURN @Input
ELSE IF @Input <= 0
RETURN 0
RETURN [dbo].[ufn_Summation] ( @Input - 1 ) + @Input
END
GO
As can be seen from the user-defined function, it calls itself making it a recursive user-defined function. Here are a couple sample outputs from calling the user-defined function:
SELECT [dbo].[ufn_Summation] ( 10 ) AS [Output]
Output
-------
55
SELECT [dbo].[ufn_Summation] ( 32 ) AS [Output]
Output
-------
528
Passing a value greater than 32 to the user-defined function will raise this error:
SELECT [dbo].[ufn_Summation] ( 33 )
Msg 217, Level 16, State 1, Line 1
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Solution / Work Around:
The current nesting level for the stored procedure, managed code reference, or user-defined function in execution is stored in the @@NESTLEVEL function. The @@NESTLEVEL function returns the nesting level of the current stored procedure, managed code reference or user-defined function execution (initially set to 0) on the local server.
One way to overcome this error message is to check the value of the @@NESTLEVEL function and immediately end the execution of the current stored procedure, managed code reference, or user-defined function without calling any other stored procedure, managed code reference, or user-defined function.
Using this solution, the recursive user-defined function earlier can be re-written as follows:
CREATE FUNCTION [dbo].[ufn_Summation] ( @Input INT )
RETURNS INT
AS
BEGIN
IF @@NESTLEVEL >= 32
RETURN @Input
IF @Input = 1
RETURN @Input
ELSE IF @Input <= 0
RETURN 0
RETURN [dbo].[ufn_Summation] ( @Input - 1 ) + @Input
END
GO
Unfortunately, using this solution will only solve the issue with regards to this error message but the output of the user-defined function will be incorrect especially when a value greater than 32 is passed as a value to the recursive user-defined function:
SELECT [dbo].[ufn_Summation] ( 10 ) -- Returns 55
SELECT [dbo].[ufn_Summation] ( 32 ) -- Returns 528
SELECT [dbo].[ufn_Summation] ( 33 ) -- Returns 560 but the correct output is 561
SELECT [dbo].[ufn_Summation] ( 34 ) -- Returns 592 but the correct output is 595
In this particular recursive user-defined function, the only solution is to rewrite the user-defined function without the use of recursion. Here’s the rewritten version of the function that outputs the same (and correct for higher input values) result:
CREATE FUNCTION [dbo].[ufn_Summation] ( @Input INT )
RETURNS INT
AS
BEGIN
DECLARE @Summation INT
SET @Summation = 0
WHILE @Input > 0
BEGIN
SET @Summation = @Summation + @Input
SET @Input = @Input - 1
END
RETURN @Summation
END
GO
Here are a few sample outputs from calling the rewritten user-defined function:
SELECT [dbo].[ufn_Summation] ( 10 ) AS [Output]
Output
-------
55
SELECT [dbo].[ufn_Summation] ( 32 ) AS [Output]
Output
-------
528
SELECT [dbo].[ufn_Summation] ( 33 ) AS [Output]
Output
-------
561
SELECT [dbo].[ufn_Summation] ( 34 ) AS [Output]
Output
-------
595
|