SQL Server递归CTE查询报错：递归深度超限问题修复

针对你用递归CTE查询Nodes表关联节点时遇到的递归深度超限制问题，有以下几种解决思路：

1. 临时调整递归深度限制 #

SQL Server默认递归深度限制为100，你可以在查询末尾添加OPTION (MAXRECURSION n)修改该值，n取值范围是0到32767，设为0表示取消深度限制。

示例代码：

WITH RecursiveNodes AS (
    -- 锚点查询：指定起始节点
    SELECT BaseDocType, BaseDocID, DocType, DocID, TargetDocType, TargetDocID
    FROM Nodes
    WHERE DocType = '你的指定类型' AND DocID = '你的指定ID'
    UNION ALL
    -- 递归查询：关联后续节点
    SELECT n.BaseDocType, n.BaseDocID, n.DocType, n.DocID, n.TargetDocType, n.TargetDocID
    FROM Nodes n
    INNER JOIN RecursiveNodes rn 
        ON n.DocType = rn.TargetDocType AND n.DocID = rn.TargetDocID
)
SELECT * FROM RecursiveNodes
OPTION (MAXRECURSION 0); -- 0表示无限制，也可设具体数值如500

注意：如果数据存在循环引用，设为0会导致无限递归耗尽资源，使用前请确认数据无环。

2. 排查并处理循环引用 #

若数据存在节点循环关联（如A→B→C→A），递归会无限执行直到触发深度限制。可在递归CTE中追踪已访问节点，避免重复处理。

示例代码：

WITH RecursiveNodes AS (
    SELECT 
        BaseDocType, BaseDocID, DocType, DocID, TargetDocType, TargetDocID,
        -- 拼接字符串记录已访问的节点标识（DocType+DocID）
        CAST(DocType + '|' + DocID AS VARCHAR(MAX)) AS VisitedNodes
    FROM Nodes
    WHERE DocType = '你的指定类型' AND DocID = '你的指定ID'
    UNION ALL
    SELECT 
        n.BaseDocType, n.BaseDocID, n.DocType, n.DocID, n.TargetDocType, n.TargetDocID,
        CAST(rn.VisitedNodes + '|' + n.DocType + '|' + n.DocID AS VARCHAR(MAX))
    FROM Nodes n
    INNER JOIN RecursiveNodes rn 
        ON n.DocType = rn.TargetDocType AND n.DocID = rn.TargetDocID
    -- 过滤已访问节点，避免循环
    WHERE CHARINDEX('|' + n.DocType + '|' + n.DocID + '|', '|' + rn.VisitedNodes + '|') = 0
)
SELECT BaseDocType, BaseDocID, DocType, DocID, TargetDocType, TargetDocID
FROM RecursiveNodes;

3. 用迭代方式替代递归 #

如果递归深度极大，可采用临时表+循环的方式逐步获取所有关联节点，规避CTE的深度限制。

示例代码：

-- 创建临时表存储结果
CREATE TABLE #AllNodes (
    BaseDocType VARCHAR(100),
    BaseDocID VARCHAR(100),
    DocType VARCHAR(100),
    DocID VARCHAR(100),
    TargetDocType VARCHAR(100),
    TargetDocID VARCHAR(100),
    PRIMARY KEY (DocType, DocID) -- 避免重复添加节点
);

-- 插入起始节点
INSERT INTO #AllNodes
SELECT BaseDocType, BaseDocID, DocType, DocID, TargetDocType, TargetDocID
FROM Nodes
WHERE DocType = '你的指定类型' AND DocID = '你的指定ID';

-- 循环获取关联节点，直到无新节点添加
WHILE @@ROWCOUNT > 0
BEGIN
    INSERT INTO #AllNodes
    SELECT n.BaseDocType, n.BaseDocID, n.DocType, n.DocID, n.TargetDocType, n.TargetDocID
    FROM Nodes n
    INNER JOIN #AllNodes an 
        ON n.DocType = an.TargetDocType AND n.DocID = an.TargetDocID
    WHERE NOT EXISTS (
        SELECT 1 FROM #AllNodes 
        WHERE DocType = n.DocType AND DocID = n.DocID
    );
END

-- 查询最终结果
SELECT * FROM #AllNodes;

-- 清理临时表
DROP TABLE #AllNodes;

内容的提问来源于stack exchange，提问作者kammushi