Using CTE to write recursive query in SQL Server 2005

CTE (Common Table Expression) is newly introduced in SQL Server 2005. The below given link elaborates the CTE and its advantages.

http://www.4guysfromrolla.com/webtech/071906-1.shtml

http://searchwindevelopment.techtarget.com/tip/0,289483,sid8_gci1278207,00.html

Basically the requirement was, when an employee logs in he needs the list of employees reporting directly under him as well as the employees who are reporting under them.

The database structure was like:

• Employee table containing the employee details like Employee Id, Name, etc.,
• Supervisor table containing the mapping of employee and their supervisor

I tried out with the CTE to build a recursive function which will get the first level reporting employees and then their reporting employee. The script was given below

Declare@UserId Int

Set @UserId = 36332

-- This is the recursive function..

WITH EmployeeHierarchy (EmployeeID, EmployeeName, HierarchyLevel) AS

(

-- Base case

Select

E.EmployeeId,

E.Name As EmployeeName,

1 As HierarchyLevel

From

Employees E

Where

E.EmployeeId = @UserId

UNION ALL

-- Recursive step

select

E.EmployeeId,

E.Name As EmployeeName,

eh.HierarchyLevel + 1 AS HierarchyLevel

FROM

Supervisors ES

INNER Join Employees E On E.EmployeeId = ES.EmployeeId INNER JOIN EmployeeHierarchy eh ON ES.Supervisor1 = eh.EmployeeID

)

-- This is the final select

Select

E.EmployeeId,

E.EmployeeNumber As EmployeeNumber,

E.EmployeeName

From

EmployeeHierarchy E

Where

E.EmployeeId <> @UserId