SQL Server Error Messages - Msg 217

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