SQL Server 2005 Beta 2 Transact-SQL 增强功能 1

来源：互连网作者：出处：巧巧读书 2006-02-24 进入讨论组

使用该级别计数器，您可以限制递归中的迭代次数。例如，以下 CTE 用来返回比 Janet 低两个级别的所有雇员：

WITH EmpCTEJanet(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 3
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees as E
JOIN EmpCTEJanet as M
ON E.mgrid = M.empid
WHERE lvl < 2
)
SELECT empid, empname
FROM EmpCTEJanet
WHERE lvl = 2

以下为结果集：

empid       empname
----------- -------------------------
11          David
12          Ron
13          Dan

该代码示例中比上一个代码示例增加的代码以粗体显示。递归成员中的筛选器 WHERE lvl < 2 被用作递归终止检查 — 当 lvl = 2 时，不会返回任何行，因而递归停止。外部查询中的筛选器 WHERE lvl = 2 用来移除上至级别 2 的所有级别。请注意，从逻辑上讲，外部查询中的筛选器 (lvl = 2) 本身就足以只返回所需的行。递归成员中的筛选器 (lvl < 2) 是出于性能原因而添加的 — 为了在返回 Janet 下的两个级别之后立即停止递归。

正如前面提到的那样，CTE 可以引用在同一批处理中定义的本地变量。例如，为了使查询更一般化，您可以使用变量而不是常量作为雇员 ID 和级别：

DECLARE @empid AS INT, @lvl AS INT
SET @empid = 3 -- Janet
SET @lvl   = 2 -- two levels
WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = @empid
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees as E
JOIN EmpCTE as M
ON E.mgrid = M.empid
WHERE lvl < @lvl
)
SELECT empid, empname
FROM EmpCTE
WHERE lvl = @lvl

您可以使用提示在已经调用一定数量的递归迭代之后强行终止查询。可以通过在外部查询的结尾添加 OPTION(MAXRECURSION value) 做到这一点，如以下示例所示：

WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 1
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees as E
JOIN EmpCTE as M
ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE
OPTION (MAXRECURSION 2)

以下为结果集：

empid       empname                   mgrid       lvl
----------- ------------------------- ----------- -----------
1           Nancy                     NULL        0
2           Andrew                    1           1
3           Janet                     1           1
4           Margaret                  1           1
10          Ina                       4           2
7           Robert                    3           2
8           Laura                     3           2
9           Ann                       3           2
.Net SqlClient Data Provider: Msg 530, Level 16, State 1, Line 1
Statement terminated. Maximum recursion 2 has been exhausted before statement completion

可能返回（但是不能保证返回）迄今生成的结果，并且生成错误 530。您可能会想到使用 MAXRECURSION 选项实现以下请求：使用 MAXRECURSION 提示而不是递归成员中的筛选器返回 Janet 下两个级别的雇员：

WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 3
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees as E
JOIN EmpCTE as M
ON E.mgrid = M.empid
)
SELECT empid, empname
FROM EmpCTE
WHERE lvl = 2
OPTION (MAXRECURSION 2)

以下为结果集：

empid       empname
----------- -------------------------
11          David
12          Ron
13          Dan
.Net SqlClient Data Provider: Msg 530, Level 16, State 1, Line 1
Statement terminated. Maximum recursion 2 has been exhausted before statement completion

但是请记住，除了不能保证返回结果以外，客户端还将获得错误。在有效场合下使用返回错误的代码不是良好的编程做法。建议您使用先前介绍的筛选器，并且如果您愿意，则请使用 MAXRECURSION 提示作为防止出现无限循环的防护措施。

当未指定该提示时，SQL Server 默认为值 100。当您怀疑存在循环递归调用时，可以使用该值作为防护措施。如果您不希望限制递归调用的次数，则请在提示中将 MAXRECURSION 设置为 0。

作为循环关系的示例，假设您的数据中有错误，并且 Nancy 的经理被意外更改为 James（而不是没有经理）：

UPDATE Employees SET mgrid = 14 WHERE empid = 1

以下循环被引入：1->3->7->11->14->1。如果您尝试运行返回 Nancy 及其所有级别的直接和间接下属的代码，则您会获得一个错误，表明默认的最大递归次数 100 在该语句完成之前耗尽：

WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 1
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE
Msg 530, Level 16, State 1, Line 1
Statement terminated. Maximum recursion 100 has been exhausted before statement completion

URl收藏 http://www.qqread.com/sqlserver/f062384005.html