uncategorized SQLException; SQL state [S0001]; error code [530]; 语句被终止。完成执行语句前已用完最大递归 100
在SQLServer中执行递归的时候出现错误
#子查父递归
With Tree As( SELECT id, parent_id FROM dbo.data_catalog C UNION ALL SELECT P.id, P.parent_id FROM dbo.data_catalog P, Tree WHERE Tree.parent_id = P.id ) SELECT * FROM data_catalog where id in (select id from Tree )
uncategorized SQLException; SQL state [S0001]; error code [530]; 语句被终止。完成执行语句前已用完最大递归 100。
原来SQLServer默认有个递归次数,如果SQL中的递归次数大于该阈值,就会触发该错误,该值默认为100,可以手动修改该值。
修改为0 则不限制次数
With Tree As( SELECT id, parent_id FROM dbo.data_catalog C UNION ALL SELECT P.id, P.parent_id FROM dbo.data_catalog P, Tree WHERE Tree.parent_id = P.id ) SELECT * FROM data_catalog where id in (select id from Tree ) OPTION (MAXRECURSION 0)