如何实现SQL存储过程的递归调用_通过MAXRECURSION选项控制递归深度

在SQL开发中，层级数据的处理是常见需求，比如组织架构的上下级查询、商品分类的多级展示等，这类场景可以通过存储过程的递归调用来高效实现，而MAXRECURSION选项则是控制递归深度的核心配置，能够避免递归无限循环导致的系统资源耗尽。

SQL存储过程递归调用的实现原理

存储过程的递归调用指的是存储过程在执行过程中调用自身的过程，通常需要设置递归终止条件，否则会进入无限循环。递归调用一般包含两个部分：递归边界（终止条件）和递归逻辑（调用自身的逻辑）。

以查询员工上下级关系为例，我们需要先定义基础查询获取顶级员工，再通过递归查询获取其下属，直到没有更多下属为止。如果没有终止条件，递归会一直执行，最终触发SQL Server的默认递归深度限制报错。

基础递归存储过程示例

以下是一个简单的递归存储过程示例，用于查询指定员工的所有下属信息，存储过程名称为usp_GetSubordinate：

-- 创建递归存储过程查询员工下属
CREATE PROCEDURE usp_GetSubordinate
    @ParentId INT  -- 上级员工ID
AS
BEGIN
    SET NOCOUNT ON;
    -- 递归终止条件：如果没有下属则不再执行
    IF NOT EXISTS (SELECT 1 FROM Employee WHERE ManagerId = @ParentId)
    BEGIN
        RETURN;
    END
    
    -- 查询当前上级的下属
    SELECT EmployeeId, EmployeeName, ManagerId 
    FROM Employee 
    WHERE ManagerId = @ParentId;
    
    -- 递归调用：查询每个下属的下属
    DECLARE @SubId INT;
    DECLARE cur_sub CURSOR FOR 
    SELECT EmployeeId FROM Employee WHERE ManagerId = @ParentId;
    OPEN cur_sub;
    FETCH NEXT FROM cur_sub INTO @SubId;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 调用自身查询下级
        EXEC usp_GetSubordinate @SubId;
        FETCH NEXT FROM cur_sub INTO @SubId;
    END
    CLOSE cur_sub;
    DEALLOCATE cur_sub;
END
GO

上述存储过程通过游标遍历每个下属，再调用自身查询下属的下属，直到没有更多下属时触发终止条件退出递归。但在SQL Server中，默认递归深度限制为100，如果层级超过100就会报错，这时候就需要用到MAXRECURSION选项。

MAXRECURSION选项的作用与用法

MAXRECURSION是SQL Server中用于限制递归查询最大深度的选项，它只能和WITH子句的递归公用表表达式（CTE）配合使用，不能直接用于上述存储过程的递归调用场景。如果要控制存储过程递归调用的深度，需要在调用存储过程时结合CTE的MAXRECURSION，或者在存储过程内部通过计数变量控制递归层级。

通过CTE+MAXRECURSION实现可控递归

更推荐的方式是使用CTE实现递归查询，再配合MAXRECURSION选项控制深度，以下是替代上述存储过程的CTE递归查询示例：

-- 使用CTE递归查询员工下属，通过MAXRECURSION控制深度
WITH EmployeeCTE AS (
    -- 锚定部分：查询指定顶级员工
    SELECT EmployeeId, EmployeeName, ManagerId, 0 AS Level
    FROM Employee
    WHERE EmployeeId = 1  -- 假设查询ID为1的员工的所有下属
    
    UNION ALL
    
    -- 递归部分：查询下属的下属
    SELECT e.EmployeeId, e.EmployeeName, e.ManagerId, cte.Level + 1
    FROM Employee e
    INNER JOIN EmployeeCTE cte ON e.ManagerId = cte.EmployeeId
)
SELECT EmployeeId, EmployeeName, ManagerId, Level
FROM EmployeeCTE
-- MAXRECURSION设置最大递归深度为200，0表示无限制
OPTION (MAXRECURSION 200);

上述代码中，CTE的锚定部分先获取顶级员工，递归部分不断关联查询下属，Level字段记录递归层级，最后通过OPTION (MAXRECURSION 200)设置最大递归深度为200，如果递归层级超过200就会直接报错终止，避免无限递归。

在存储过程中结合计数控制递归深度

如果必须使用存储过程递归调用，也可以在存储过程中添加层级计数参数，手动控制递归深度，示例如下：

-- 带深度控制的递归存储过程
CREATE PROCEDURE usp_GetSubordinate_WithDepth
    @ParentId INT,
    @CurrentDepth INT = 0,  -- 当前递归深度，默认从0开始
    @MaxDepth INT = 100     -- 最大允许递归深度，默认100
AS
BEGIN
    SET NOCOUNT ON;
    -- 深度超过最大值则终止递归
    IF @CurrentDepth > @MaxDepth
    BEGIN
        RETURN;
    END
    
    -- 查询当前上级的下属
    SELECT EmployeeId, EmployeeName, ManagerId, @CurrentDepth AS Level
    FROM Employee 
    WHERE ManagerId = @ParentId;
    
    -- 递归调用，深度加1
    DECLARE @SubId INT;
    DECLARE cur_sub CURSOR FOR 
    SELECT EmployeeId FROM Employee WHERE ManagerId = @ParentId;
    OPEN cur_sub;
    FETCH NEXT FROM cur_sub INTO @SubId;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC usp_GetSubordinate_WithDepth @SubId, @CurrentDepth + 1, @MaxDepth;
        FETCH NEXT FROM cur_sub INTO @SubId;
    END
    CLOSE cur_sub;
    DEALLOCATE cur_sub;
END
GO

调用该存储过程时，可以传入@MaxDepth参数自定义最大递归深度，比如EXEC usp_GetSubordinate_WithDepth 1, 0, 150就表示最多递归150层，超过则自动终止。

注意事项

• 递归调用必须设置明确的终止条件，否则无论是否使用MAXRECURSION，都可能导致系统资源耗尽。
• MAXRECURSION选项仅在SQL Server中生效，其他数据库如MySQL、PostgreSQL有各自的递归控制方式，需要根据对应数据库的语法调整。
• 如果设置MAXRECURSION为0，表示不限制递归深度，仅在有明确终止条件时使用，否则会触发无限递归报错。
• 递归层级过深时即使不报错，也会影响查询性能，建议尽量优化层级结构，避免过深的递归设计。

总结

SQL存储过程的递归调用可以通过自身调用实现，也可以通过CTE的递归查询实现，而MAXRECURSION选项是SQL Server中控制递归深度的有效手段，通常配合CTE使用。实际开发中建议优先使用CTE+MAXRECURSION的方式实现层级数据查询，既简洁又能灵活控制递归深度，避免递归异常问题。如果需要使用存储过程递归，也可以通过添加深度计数参数的方式手动控制，确保递归在可控范围内执行。

SQL存储过程递归调用MAXRECURSION修改时间：2026-06-08 15:45:33