SQL SERVER:选项(MAXRECURSION n)不使用动态变量

基础概念

MAXRECURSION 是 SQL Server 中用于控制递归查询的最大递归深度的选项。默认情况下，SQL Server 允许的最大递归深度为 100。通过设置 MAXRECURSION，可以限制递归查询的深度，以避免无限递归导致的性能问题或错误。

类型

MAXRECURSION 可以设置为以下几种类型：

整数：指定具体的递归深度，如 MAXRECURSION 5。
0：表示无限制递归，但通常不推荐使用，因为可能导致性能问题。
DEFAULT：使用默认值 100。

应用场景

层次数据查询：在处理具有层次结构的数据（如组织结构、产品分类等）时，递归查询非常有用。
数据清洗：在某些数据清洗任务中，可能需要通过递归方式处理嵌套数据。
复杂逻辑处理：在需要通过递归实现复杂逻辑的场景中，如计算路径、遍历树结构等。

示例代码

假设我们有一个员工表 Employees，其中包含员工的 ID 和其直接上级的 ID，我们希望查询某个员工的所有上级路径。

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    ManagerID INT
);

INSERT INTO Employees (EmployeeID, ManagerID) VALUES
(1, NULL),
(2, 1),
(3, 2),
(4, 3);

-- 查询员工 4 的所有上级路径，限制递归深度为 5
WITH RecursivePath AS (
    SELECT EmployeeID, ManagerID, CAST(EmployeeID AS VARCHAR(MAX)) AS Path
    FROM Employees
    WHERE EmployeeID = 4
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, CAST(r.Path + ' -> ' + CAST(e.EmployeeID AS VARCHAR(MAX)) AS VARCHAR(MAX))
    FROM RecursivePath r
    INNER JOIN Employees e ON r.ManagerID = e.EmployeeID
    WHERE r.Path NOT LIKE '%' + CAST(e.EmployeeID AS VARCHAR(MAX)) + '%'
)
SELECT * FROM RecursivePath OPTION (MAXRECURSION 5);

遇到问题及解决方法

问题：递归查询进入无限循环

原因：递归查询中的条件设置不当，导致查询无法终止。

解决方法：

检查递归条件：确保每次递归调用都能逐步接近终止条件。
设置 MAXRECURSION：通过设置合理的递归深度限制，防止无限递归。

-- 设置 MAXRECURSION 为 5，防止无限递归
WITH RecursivePath AS (
    SELECT EmployeeID, ManagerID, CAST(EmployeeID AS VARCHAR(MAX)) AS Path
    FROM Employees
    WHERE EmployeeID = 4
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, CAST(r.Path + ' -> ' + CAST(e.EmployeeID AS VARCHAR(MAX)) AS VARCHAR(MAX))
    FROM RecursivePath r
    INNER JOIN Employees e ON r.ManagerID = e.EmployeeID
    WHERE r.Path NOT LIKE '%' + CAST(e.EmployeeID AS VARCHAR(MAX)) + '%'
)
SELECT * FROM RecursivePath OPTION (MAXRECURSION 5);

通过这种方式，可以有效控制递归查询的行为，避免性能问题和系统崩溃。