Recursive CTE is one of the important enhancements in SQL Server 2005. Recursive queries are generally used when dealing with tree, graph, and hierarchy problems.
The syntax of CTE is as follows:
1 with CTE
2 (
3 select empid, reportto, fname from employ where empid = 1
4 union all
5 select EMP. empid, EMP. reportto, EMP. fname from CTE join employ as EMP on CTE. empid = EMP. reportto
6)
Recursive CTE contains at least two queries (also called members ). The first query is a fixed-point member. A fixed-point member is only a query that returns a valid table and is used as the basis or positioning point for recursion. The second query is called a recursive member, and the recursive reference to the CTE name is triggered. Logically, the internal application of the CTE name can be understood as the result set of the previous query.
Recursive queries do not have explicit recursive termination conditions. Recursive queries are stopped only when the second recursive query returns an empty result set or exceeds the maximum number of recursion times. Maxrecurion is used to limit the number of recursion times.
1 use adventureworks;
2 go
3 -- creates an infinite loop
4 with CTE (employeeid, managerid, title)
5 (
6 select employeeid, managerid, title
7 from HumanResources. Employee
8 where managerid is not null
9 Union all
10 select CTE. employeeid, CTE. managerid, CTE. Title
11 from CTE
12 join HumanResources. employee as E
13 on CTE. managerid = E. employeeid
14)
15 -- uses maxrecursion to limit the recursive levels to 2
16 select employeeid, managerid, title
17 from CTE
18 option (maxrecursion 2 );
19 go