Back to Papers and Articles Using SQL Server 2000's User Defined Function to solve the Tree Problem Copyright 2001 Paragon Corporation   ( April 16, 2001) Tree problems have plagued SQL Server db programmers for a long time. The new user defined function feature in SQL Server 2000 provides a relief to this dilemma. In this article we demonstrate an approach to solving the employee supervisor tree problem using a recursive user-defined SQL Server 2000 function call. The Problem Suppose you have an employee called John and John directly reports Nancy. Nancy directly reports to Peter and Peter directly reports to Diana. The table you have set up to model this relationship has two fields for simplicity EmployeeID varchar(50), ManagerID varchar(50). In your table are the following entries ``` EmployeeID ManagerID Diana  PeterDiana NancyPeter JohnNancy ``` Now John is a very insecure person and knows he is way down on the food chain. He requests a report from you that gives him a synopsis of where he stands in the food chain of the company to figure out how far he needs to climb. You think about it for a minute and come up with this recursive function that given a person's employee id will provide a list of all his direct and indirect supervisors ordered by rank. The function looks as follows `````` CREATE FUNCTION ReportsTo (@EmployeeID varchar(50), @depth int) RETURNS @retFindReports TABLE (EmployeeID varchar(50) primary key, depth int) AS BEGIN DECLARE @ManagerID varchar(50) DECLARE @nextDepth int SET @nextDepth = @depth + 1 SET @ManagerID = (SELECT ManagerID FROM Employees WHERE EmployeeID = @EmployeeID) IF @ManagerID IS NOT NULL and @ManagerID > '' and @depth <= 5 and @ManagerID <> @EmployeeID /***We stop if a person is their own manager or a person has no manager or We've exceeded a depth of 5 (to prevent potential infinite recursion ***/ INSERT INTO @retFindReports(EmployeeID, depth) SELECT EmployeeID , @depth As Depth FROM Employees M Where M.EmployeeID = @ManagerID UNION --Recurseively call the function to append on the manager of a person's manager until no more SELECT EmployeeID, Depth FROM ReportsTo(@managerID, @nextDepth) RETURN END ``` ``` To figure out who John's supervisors are in order of rank you perform the following select statement ``` SELECT * From reportsTo('John',0) order by depth DESC ``` This returns ```Diana 2 Peter 1 Nancy 0 ``` Back to Papers and Articles /** Navigation **/ ul#nav { list-style:none; margin:3px 0 1em; text-align:center; background:#fff; padding-bottom:1px; border-top:2px solid #FF9933; border-bottom:2px solid #FF9933; height: 25px; width: 95%; bottom:0; } #nav li { list-style:none; width:16.5%; float:left; display:block; text-align:center; background:#EAF0E6; } #nav a { display:block; font:bold 1em/1.8 'Lucida Grande', Arial, tahoma, verdana, sans-serif; text-decoration:none; color:#006699; margin:1px; } #nav a:focus, #nav a:hover, #nav a:active { border:1px inset #8B9F86; margin:0; color:#EB8518; text-decoration:underline; } /** current menu item **/ ul#nav li#selectedItem a#selectedItem{ background:#330033; font:bold 1em/1.8 'Lucida Grande', Arial, tahoma, verdana, sans-serif; color:#000; text-decoration:underline; } Last rejuvenated: 9/24/2017 3:23:37 PM        © 2017 Paragon Corporation   All Rights Reserved