Reputation: 33
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
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)
Further to that maybe have a look at
Using Common Table Expressions and Recursive Queries Using Common Table Expressions
Also,
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