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

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

当然，具有能够预防无限递归调用的安全措施是很好的，但是 MAXRECURSION 在隔离循环和解决数据中的错误方面不能提供多少帮助。为了隔离循环，您可以使用相应的 CTE，以便为每个雇员构建通向该雇员的所有雇员 ID 的枚举路径。调用该结果列路径。

在递归成员中，使用 CASE 表达式和 LIKE 谓词检查当前雇员 ID 是否已经出现在经理的路径中。如果答案是肯定的，则意味着您找到了循环。如果找到了循环，则在名为 cycle 的结果列中返回 1，否则返回 0。而且，向递归成员中添加一个筛选器，以确保只返回未检测到循环的经理的下属。最后，向外部查询中添加一个筛选器，以便只返回找到循环的雇员 (cycle = 1)：

WITH EmpCTE(empid, path, cycle)
AS
(
SELECT empid,
CAST('.' + CAST(empid AS VARCHAR(10)) + '.' AS VARCHAR(900)),
0
FROM Employees
WHERE empid = 1
UNION ALL
SELECT E.empid,
CAST(M.path + CAST(E.empid AS VARCHAR(10)) + '.' AS VARCHAR(900)),
CASE
WHEN M.path LIKE '%.' + CAST(E.empid AS VARCHAR(10)) + '.%' THEN 1
ELSE 0
END
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
WHERE M.cycle = 0
)
SELECT path FROM EmpCTE
WHERE cycle = 1
path
---------------
.1.3.7.11.14.1.

请注意，锚定成员和递归成员中的对应列必须具有相同的数据类型、长度和精度。这就是生成 path 值的表达式在两个成员中都被转换为 varbinary(900) 的原因。一旦检测到循环，您就可以通过将 Nancy 的经理重新更改为没有经理来修复数据中的错误：

UPDATE Employees SET mgrid = NULL WHERE empid = 1

迄今为止提供的递归示例具有一个经理锚定成员和一个用于检索下属的递归成员。某些请求则要求执行相反的操作；例如，请求返回 James 的管理路径（James 及其所有级别的经理）。以下代码提供了对该请求的应答：

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

以下为结果集：

empid       empname                   mgrid       lvl
----------- ------------------------- ----------- -----------
14          James                     11          0
11          David                     7           1
7           Robert                    3           2
3           Janet                     1           3
1           Nancy                     NULL        4

锚定成员返回 James 的行。递归成员返回先前返回的雇员或经理的单个经理，因为这里使用的是单父节点层次结构并且请求从单个雇员开始。

URL:http://www.qqread.com/sqlserver/f062384005.html