SQL Tips by Namwar Rizvi

May 17, 2007

Retrieving Employee Hierarchy through CTE

Filed under: CTE,New Features,Query,tips,TSQL,UDF — namwar @ 8:44 PM

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

Advertisements

1 Comment »

  1. Great tip! Very Useful Namwar Rizvi.

    Cheers

    Comment by sql student — August 31, 2011 @ 11:00 AM | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: