Retrieving Employee Hierarchy through CTE

Retrieving subordinates of a given employee or listing sub-nodes of the given node is a classic TSQL problem. This problem can be solved either iteratively by using cursor or recursively by using UDF. Another recursive approach which is now available in SQL Server 2005 instead of UDF is CTE or Common Table Expressions.

Common Table Expression is a much cleaner approach and is highly recommended in these scenarios. Following is a TSQL stored procedure for AdventureWorks database which have typical
EmployeeId and ManagerId columns table to store hierarchy of employees. Following is the code:

Create Proc usp_GetSubordinates(@p_ManagerId int)
as
Begin

With SubCTE
As
(
Select EmployeeId,P.FirstName+‘ ‘ + P.LastName as FullName, 0 as level
From HumanResources.Employee E Inner Join Person.Contact P
ON E.ContactId=P.ContactId
Where EmployeeId=@p_ManagerId

Union All

Select C.EmployeeId,PP.FirstName+‘ ‘ + PP.LastName as FullName, P.level+1
From SubCTE as P
Inner Join HumanResources.Employee C
ON C.ManagerId=P.EmployeeId
Inner Join Person.Contact PP
ON C.ContactId=PP.ContactId

)

Select * from SubCTE;

End