Recursive SQL statment using CTE "With as"

having such a table in a MS SQL Server 2005

where the table represents some employees and their tree structure in the organization, where each employee has a foreign key "ManagerID" which is  the ID of another Employee in the same table, and this table is represented with the following tree

the problem is that i want to select the employees with an additional field holding their level in the previous tree a recursive solution, huh !! ok, that can be solved using recursive SQL function but here is another approach...

just SQL select statement that uses CTE "Common Table Expressions" introduced in MS SQL Server 2005 -yeah i know i'm too late :( - so here is the SQL Select Query

--select the root column(s)
--The anchor member
SELECT     Employee.EmpID, Employee.ManagerID, Employee.Name, 0 AS LEVEL
 FROM         Employee
 WHERE     ManagerID IS NULL

--select the children recursively
--The recursive member
 SELECT     Employee.EmpID, Employee.ManagerID, Employee.Name, LEVEL + 1 AS LEVEL
 FROM         Employee JOIN t ON t .EmpID = Employee.ManagerID
 SELECT     * FROM t

the term "With t as" is the CTE which allows us to create a table on the fly named t, where this table is filled using the query between the preceding brackets.

as any normal recursion, there must be a starting point and a terminating condition the starting point here is the Anchor Member which is the first select query that gets the Employees whose "ManagerID" is NULL "they are the managers" then the statement "Union All" that applies the next query over and over till it returns nothing "which is the terminating condition here" and the recursive member here uses the selected rows in the table "t" till the moment to join on the ManagerID in the Employee table with the ID of the already selected Managers in t in the last execution of the recursive member running such query on the table "Employee" will produce the following result set

i hope that helps... and here is my reference

Nice article Abdallah! Thanks so muuch
Thanks! finally found a solution to my problem! this helped me a lot! keep it!