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

核心提示： 使用该级别计数器，您可以限制递归中的迭代次数，SQL Server 2005 Beta 2 Transact-SQL 增强功能 1(7)，例如，以下 CTE 用来返回比 Janet 低两个级别的所有雇员：WITH EmpCTEJanet(empid, empname, mgrid, lvl)AS(SELECT emp

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

WITH EmpCTEJanet(empid, empname, mgrid, lvl)AS(SELECT empid, empname, mgrid, 0FROM EmployeesWHERE empid = 3UNION ALLSELECT E.empid, E.empname, E.mgrid, M.lvl+1FROM Employees as EJOIN EmpCTEJanet as MON E.mgrid = M.empidWHERE lvl < 2)SELECT empid, empnameFROM EmpCTEJanetWHERE lvl = 2

以下为结果集：

empid       empname----------- -------------------------11          David12          Ron13          Dan

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

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

DECLARE @empid AS INT, @lvl AS INTSET @empid = 3 -- JanetSET @lvl   = 2 -- two levelsWITH EmpCTE(empid, empname, mgrid, lvl)AS(SELECT empid, empname, mgrid, 0FROM EmployeesWHERE empid = @empidUNION ALLSELECT E.empid, E.empname, E.mgrid, M.lvl+1FROM Employees as EJOIN EmpCTE as MON E.mgrid = M.empidWHERE lvl < @lvl)SELECT empid, empnameFROM EmpCTEWHERE lvl = @lvl

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

WITH EmpCTE(empid, empname, mgrid, lvl)AS(SELECT empid, empname, mgrid, 0FROM EmployeesWHERE empid = 1UNION ALLSELECT E.empid, E.empname, E.mgrid, M.lvl+1FROM Employees as EJOIN EmpCTE as MON E.mgrid = M.empid)SELECT * FROM EmpCTEOPTION (MAXRECURSION 2)

以下为结果集：

empid       empname                   mgrid       lvl----------- ------------------------- ----------- -----------1           Nancy                     NULL        02           Andrew                    1           13           Janet                     1           14           Margaret                  1           110          Ina                       4           27           Robert                    3           28           Laura                     3           29           Ann                       3           2.Net SqlClient Data Provider: Msg 530, Level 16, State 1, Line 1Statement 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, 0FROM EmployeesWHERE empid = 3UNION ALLSELECT E.empid, E.empname, E.mgrid, M.lvl+1FROM Employees as EJOIN EmpCTE as MON E.mgrid = M.empid)SELECT empid, empnameFROM EmpCTEWHERE lvl = 2OPTION (MAXRECURSION 2)

以下为结果集：

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

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

当未指定该提示时，sql server(WINDOWS平台上强大的数据库平台) 默认为值 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, 0FROM EmployeesWHERE empid = 1UNION ALLSELECT E.empid, E.empname, E.mgrid, M.lvl+1FROM Employees AS EJOIN EmpCTE AS MON E.mgrid = M.empid)SELECT * FROM EmpCTEMsg 530, Level 16, State 1, Line 1Statement terminated. Maximum recursion 100 has been exhausted before statement completion