Finding all Managers of the given employee thourgh Recursive CTE

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