asbd
asbd

Reputation: 33

how to deal with "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)."

i was asked to Create script that will expect whoever runs it to provide an employee id. Locate all employees that the provided employee supervises in any depth.

My code is :

CREATE FUNCTION [dbo].[GetNames] (@V uniqueidentifier)  
RETURNS @OldNames TABLE (EMP_NAME varchar(50))
 AS 
BEGIN
    DECLARE @master uniqueidentifier
    SET @master=(SELECT EMP_Supervisor FROM Employee WHERE EMP_ID=@v)
    IF @master=NULL return

    INSERT INTO @OldNames(EMP_NAME)
        SELECT (SELECT EMP_NAME FROM Employee WHERE EMP_ID = @master)
        FROM Employee
        UNION
        SELECT EMP_NAME FROM GetNames(@master)
     RETURN
END

And when i want to see if it works, i execute this :

SELECT * from GetNames('561e2d88-a747-460f-99e1-cfb1d3d8ca5c')

where "561e2d88-a747-460f-99e1-cfb1d3d8ca5c" is an ui of an employee and i get this as an exception:

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

can you please help me? Thanks in advance!!

Upvotes: 3

Views: 33393

Answers (1)

Adriaan Stander
Adriaan Stander

Reputation: 166486

Lets say you are using SQL Server

Have a look at the following example

DECLARE @EmployeeStructure TABLE(
        ID INT,
        Name VARCHAR(MAX),
        ManagerID INT
)

INSERT INTO @EmployeeStructure SELECT 1, 'a', NULL
INSERT INTO @EmployeeStructure SELECT 2, 'b', 1
INSERT INTO @EmployeeStructure SELECT 3, 'c', 1
INSERT INTO @EmployeeStructure SELECT 4, 'd', 2
INSERT INTO @EmployeeStructure SELECT 5, 'e', 2
INSERT INTO @EmployeeStructure SELECT 6, 'f', 2

DECLARE @EmployeeID INT = 2

;WITH Employee AS (
        SELECT  Name, ID
        FROM    @EmployeeStructure e 
        WHERE   ManagerID = @EmployeeID
        UNION ALL
        SELECT  es.Name,
                es.ID
        FROM    Employee e INNER JOIN
                @EmployeeStructure es   ON  e.ID = es.ManagerID
)
SELECT  Name
FROM    Employee
OPTION (MAXRECURSION 0)

SQL Fiddle Demo

Further to that maybe have a look at

Using Common Table Expressions and Recursive Queries Using Common Table Expressions

Also,

Query Hints (Transact-SQL)

MAXRECURSION number

Specifies the maximum number of recursions allowed for this query. number is a nonnegative integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is not specified, the default limit for the server is 100.

When the specified or default number for MAXRECURSION limit is reached during query execution, the query is ended and an error is returned.

Because of this error, all effects of the statement are rolled back. If the statement is a SELECT statement, partial results or no results may be returned. Any partial results returned may not include all rows on recursion levels beyond the specified maximum recursion level.

Upvotes: 4

Related Questions