递归SQL查询添加OPTION(MAXRECURSION 0)后语法错误求助

问题原因拆解 #

1. 第一个报错The statement terminated. The maximum recursion 100 has been exhausted before statement completion：
   SQL Server默认给递归CTE设置的最大递归深度是100，你的层级数据超过了这个限制，所以触发报错。

2. 第二个报错Incorrect syntax near the keyword 'OPTION'：
   核心问题是**OPTION (MAXRECURSION ...)不能放在CTE的定义内部**，它属于整个SQL语句的执行选项，必须放在整个查询的最后，不能跟在CTE的子查询后面。

错误与正确写法对比 #

错误写法（OPTION放错位置）

WITH q1 AS (
    SELECT id, parent_id, name FROM your_table WHERE parent_id IS NULL
),
q2 AS (
    SELECT id, parent_id, name FROM q1
    UNION ALL
    SELECT t.id, t.parent_id, t.name FROM your_table t
    JOIN q2 ON t.parent_id = q2.id
    OPTION (MAXRECURSION 0) -- 这里是错误位置！
)
SELECT * FROM q2;

正确写法（OPTION放在整个查询末尾）

WITH q1 AS (
    SELECT id, parent_id, name FROM your_table WHERE parent_id IS NULL
),
q2 AS (
    SELECT id, parent_id, name FROM q1
    UNION ALL
    SELECT t.id, t.parent_id, t.name FROM your_table t
    JOIN q2 ON t.parent_id = q2.id
)
SELECT * FROM q2
OPTION (MAXRECURSION 0); -- 放在整个SELECT语句最后才正确

额外注意事项 #

• 使用MAXRECURSION 0意味着关闭递归次数限制，务必确保你的递归CTE有明确的终止条件（比如关联到不存在的父ID时停止递归），否则会陷入无限循环，耗尽数据库资源。
• 如果能预估数据的最大层级，建议设置一个具体的数值（比如MAXRECURSION 500），比直接用0更安全。

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