2007-05-27

T-SQL - Recursion Levels / Selecting Tables Managers from an Employee Table

You've probably worked with recursive relationships at one point or another, and sometimes you need to compile advanced recursive datasets based on this. Perfect example of this is the age-old Employee / Manager table. See:
Employee
EmployeeID
EmployeeName
ManagerID
In this example, ManagerID is a foreign key to the EmployeeID, so that they child record is another employee in the same table.

Here's the code, sans explanation. Details of the With/CTE can be found on MSDN at http://msdn2.microsoft.com/en-us/library/ms175972.aspx. If I get some time before I forgot, I'll come back and explain this.
CREATE PROCEDURE [GetEmployeeManagerHeirarchy]
@EmployeeID INT
AS
BEGIN

WITH [EMP_cte]
(
[EmployeeID]
, [ManagerID]
, [EmployeeName]
, [RecursionLevel]
) -- CTE name and columns
AS
(
SELECT
e.[EmployeeID]
, e.[ManagerID]
, e.[EmployeeName]
, 0 -- Get the initial Employee
FROM [Employee] e

WHERE e.[EmployeeID] = @EmployeeID

UNION ALL

SELECT
e.[EmployeeID]
, e.[ManagerID]
, e.[EmployeeName]
, [RecursionLevel] + 1 -- Join recursive member
to anchor
FROM [Employee] e

INNER JOIN [EMP_cte]
ON e.[EmployeeID] = [EMP_cte].[ManagerID]
)
-- Join back to Employee to return the manager name
SELECT [EMP_cte].[RecursionLevel]
, [EMP_cte].[EmployeeID]
, [EMP_cte].[EmployeeName]
, [EMP_cte].[ManagerID]
, c.[EmployeeName] AS 'ManagerName' -- Outer select from the CTE
FROM [EMP_cte]

INNER JOIN [Employee] e
ON [EMP_cte].[ManagerID] = e.[EmployeeID]

ORDER BY
[RecursionLevel]
, [ManagerID]
, [EmployeeID]
OPTION (MAXRECURSION 25)

END;

No comments: