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

Question

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!!

What database are you using? Using recursion in a stored procedure is a sure way to get into trouble, and a bad design to start with. What you are looking for is a way to search in a hierarchy. There are multiple ways to do this, some of which depend on the particular SQL dialect you are using — Panagiotis Kanavos, May 10 '13 at 11:27
http://stackoverflow.com/questions/959804/simulation-of-connect-by-prior-of-oracle-in-sql-server (this appears to be SQL server consider using the with statement as in the example linked — xQbert, May 10 '13 at 11:28
Seems highly unusual to have an employee->manager hierarchy with 32 levels anyway. Sure you don't have a cycle in the data? — Martin Smith, May 10 '13 at 11:30
I m using SQL Server I was given a database and 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, as i wrote earlier. and the employee table is this : CREATE TABLE [dbo].[Employee]( [EMP_ID] [uniqueidentifier] NOT NULL, [EMP_Name] [nvarchar](100) NOT NULL, [EMP_DateOfHire] [datetime] NOT NULL, [EMP_Supervisor] [uniqueidentifier] NULL) — asbd, May 10 '13 at 11:43

score 4 · Accepted Answer · answered May 10 '13 at 11:29

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.

This is a nice example but the id and the manager type is uniqueidentifier not int.. — asbd, May 10 '13 at 11:40

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

1 Answers1

SQL Fiddle Demo

Linked