SQLServer CTE 递归查询限制递归级数（完成执行语句前已用完最大递归）

SQLServer CTE 递归查询限制递归级数（完成执行语句前已用完最大递归）

介绍

如果递归 CTE 组合不正确，可能会导致无限循环。 例如，如果递归成员查询定义对父列和子列返回相同的值，则会造成无限循环。 可以使用 MAXRECURSION 来限制特定语句所允许的递归级数，以防止出现无限循环。 这样就能够在解决产生循环的代码问题之前控制语句的执行。

MAXRECURSION <integer_value>

指定该查询允许的最大递归数。 <integer_value> 是介于 0 至 32,767 之间的非负整数。 如果指定 0，则没有限制。 如果未指定此选项，服务器的默认限制为 100。

如果在查询执行期间达到指定或默认的 MAXRECURSION 数量限制，查询结束并返回错误。

由于此错误，该语句的所有结果都被回滚。 如果该语句为 SELECT 语句，则可能会返回部分结果或不返回结果。 所返回的任何部分结果都可能无法包括超过指定最大递归级别的递归级别上的所有行。

参考文档

递归公用表表达式的准则

MAXRECURSION

示例

创建测试数据

create table #ceshi(id int, pid int, mc varchar(20))
insert into #ceshi
  values(1, 0, '1级科目_1'), (2, 0, '1级科目_2'),
        (3, 1, '2级科目_1'), (4, 2, '2级科目_2'), 
		(5, 3, '3级科目_1'), (6, 4, '3级科目_2'), 
		(7, 5, '4级科目_1'), (8, 6, '4级科目_2') 

设置只递归查询2次（默认值为 100）

WITH 
  cte AS
  (Select *, id * 10 sxh from #ceshi where pid = 0
   union all
   Select A.*, sxh + 1 from #ceshi A INNER JOIN cte B ON A.pid = B.id
)
select * from cte order by sxh OPTION (MAXRECURSION 2)

错误提示

消息 530，级别 16，状态 1，第 1 行
语句被终止。完成执行语句前已用完最大递归 2。

修改为不限制递归查询次数

WITH 
  cte AS
  (Select *, id * 10 sxh from #ceshi where pid = 0
   union all
   Select A.*, sxh + 1 from #ceshi A INNER JOIN cte B ON A.pid = B.id
)
select * from cte order by sxh OPTION (MAXRECURSION 0)

查询结果

id	pid	mc	sxh
1	0	1级科目_1	10
3	1	2级科目_1	11
5	3	3级科目_1	12
7	5	4级科目_1	13
2	0	1级科目_2	20
4	2	2级科目_2	21
6	4	3级科目_2	22
8	6	4级科目_2	23