SQL Server2005 uses a CTE to implement recursion

This article from: http://www.cnblogs.com/wenjl520/archive/2010/01/18/1650393.html

CTE recursion principle:

The recursive CTE is constructed from two minimum queries. The first is a positional member (Anchor member,am), which is a non-recursive query, and the second is a recursive member (Recursive MEMBER,RM), which is a recursive query. In the CTE brackets (after the AS clause), A query that defines a stand-alone query or reference back to the same CTE, which is delimited by the UNION ALL statement. Am is called once, and RM is called repeatedly until the query does not return data. You can use the union or union ALL operator to append multiple am to each other. Depending on whether you want to delete duplicate data (you must use the union ALL operator to append recursive members). The syntax is as follows:

--Need to query the field collection and also the field collection of query results
With simplerecursive (filed names)
As
(
--Query Location member table
<select Statement for the Anchor member>
UNION All
--Query the recursive member table
<select Statement for the Recursive member>
)
SELECT * from Simplerecursive

Example: We will create an employee table and a self-referencing field named ReportsTo, which references back to emloyee_id, and then write a query that returns like Stephen (employee_id=2) All employees who report and all employees who report to Stephen's subordinates.

Code Listing:

--Create a table
CREATE TABLE Employee_tree
(
EMPLOYEE_NM NVARCHAR (50),
employee_id INT PRIMARY KEY,
ReportsTo INT
)

INSERT into Employee_tree
VALUES (' Richard ', 1, NULL)
INSERT into Employee_tree
VALUES (' Stephen ', 2, 1)
INSERT into Employee_tree
VALUES (' Clemens ', 3, 2)
INSERT into Employee_tree
VALUES (' Malek ', 4, 2)
INSERT into Employee_tree
VALUES (' Goksin ', 5, 4)
INSERT into Employee_tree
VALUES (' Kimberly ', 6, 1)
INSERT into Employee_tree
VALUES (' Ramesh ', 7, 5)
--Create a recursive query
With Simplerecursive (EMPLOYEE_NM, employee_id, ReportsTo)
As (SELECT employee_nm,
EMPLOYEE_ID,
ReportsTo
From Employee_tree
WHERE employee_id = 2
UNION All
SELECT p.employee_nm,
P.EMPLOYEE_ID,
P.reportsto
From Employee_tree P
INNER JOIN simplerecursive A on a.employee_id = P.reportsto
)
SELECT Sr. employee_id as Empid,
Sr. Employee_nm as EMP,
Et. EMPLOYEE_NM as Boss
From Simplerecursive SR
INNER JOIN Employee_tree et on Sr. ReportsTo = et. employee_id

Query Result:

The recursive process begins at the location of the employee_id=2 (the definition member or the first select). It gets the record and uses the recursive recursive member (select after UNION all) to get all the records reported to Stephen and the child records of that record. (Goksin reports to Malek, Malek reports to Stephen). Each subsequent recursion tries to find more child records. They are the parent record of the employee that was previously found. Eventually the recursion does not return a result, which is the cause of the recursive stop ( Why there is no return to Kimberly). If the anchor member is changed to Employee_id=1, the Kimberly will also be returned in the result.

By design, recursive members will always look for child records and can loop indefinitely. If you suspect that there will be many loops and you want to limit the number of recursive calls, you can use the OPTION clause to specify the MAXRECURSION option after the outer query.

OPTION (Maxrecursion 25)

This option causes SQL server2005 to produce an error when the CTE has a specified limit. By default, the limit is 100 (that is, when this option is omitted). To specify an option, you must set Maxrecursion to 0.

Code Listing:

With Simplerecursive (EMPLOYEE_NM, employee_id, ReportsTo)
As (SELECT employee_nm,
EMPLOYEE_ID,
ReportsTo
From Employee_tree
WHERE employee_id = 2
UNION All
SELECT p.employee_nm,
P.EMPLOYEE_ID,
P.reportsto
From Employee_tree P
INNER JOIN simplerecursive A on a.employee_id = P.reportsto
)
SELECT Sr. employee_id as Empid,
Sr. Employee_nm as EMP,
Et. EMPLOYEE_NM as Boss
From Simplerecursive SR
INNER JOIN Employee_tree et on Sr. ReportsTo = et. employee_id
OPTION (maxrecursion 2)

The results are as follows:

You will also see the following error:

MSG 530, Level 16, State 1, line 1th
Statement is terminated. The maximum recursion 2 was exhausted before the execution of the statement was completed.
One way to avoid this exception is to use one of the resulting columns to trace the level at which it resides, and to include it in the WHERE clause instead of maxrecursion. The following results are the same as the previous example, but do not produce an error.

Code Listing:

With Simplerecursive (EMPLOYEE_NM, employee_id, ReportsTo, Sublevel)
As (SELECT employee_nm,
EMPLOYEE_ID,
ReportsTo,
0
From Employee_tree
WHERE employee_id = 2
UNION All
SELECT p.employee_nm,
P.EMPLOYEE_ID,
P.reportsto,
Sublevel + 1
From Employee_tree P
INNER JOIN simplerecursive A on a.employee_id = P.reportsto
)
SELECT Sr. employee_id as Empid,
Sr. Employee_nm as EMP,
Et. EMPLOYEE_NM as Boss
From Simplerecursive SR
INNER JOIN Employee_tree et on Sr. ReportsTo = et. employee_id
WHERE Sublevel <=2

SQL Server2005 uses a CTE to implement recursion