SQL Tips by Namwar Rizvi

June 23, 2007

Finding all Managers of the given employee thourgh Recursive CTE

Filed under: Uncategorized — namwar @ 10:36 PM

Common Table Expressions or CTE are not just for writing more manageable queries. They are lot more than this, recursion is one of the features you can now use by CTE based queries. Remember your school days! Recursion is the solution of many complex or lengthy algorithms like factorial, finding employee hierarchy, finding parent nodes, finding child nodes etc.

SQL Server 2005 CTE based recursive queries are native to the TSQL engine and are very efficient also. Recursive CTE query is composed of two parts:

1. Anchor query, this query starts the recursion or you can say provides seed row for the recursion
2. Recursive query, this query returns the result based on the join condition with the anchor query.

Non-Recursive condition:
As you may already know that recursion always need a non-recursive condition or exit condition otherwise it will go in infinite loop. In Recursive CTE, Non-recursive condition is implicit; it will automatically stop when last run will not return any row.

You can better understand this by running the following query which can be used to return all managers of the given employee.

Use AdventureWorks
Go

With OrgChart as
(
–Acnhor Query
Select EmployeeId,LoginId,ManagerId from HumanResources.Employee Where EmployeeId=4
Union all
–Recursive Query
Select E.EmployeeId,E.LoginId,E.ManagerId from HumanResources.Employee E
Inner Join OrgChart M –Joining with anchor member
On E.EmployeeId=M.ManagerId

)

–Returning all managers of EmployeeId 4
Select * from OrgChart

For detailed study of Recursive CTE please refer to the following resources:

Recursive CTE on MSDN
Excellent article for Recursive CTE on SQL Server Central

Advertisements

Leave a Comment »

No comments yet.

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: