SQL 最大递归 100 在语句完成之前已用完
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9650045/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
The maximum recursion 100 has been exhausted before statement completion
提问by HELP_ME
I keep getting a max recursion errorwith this query.
我不断收到max recursion error这个查询。
At first I thought it was because a null was being returned and then it would try and match the null values causing the error however, I rewrote my query so nulls aren't returned and the error still occurs.
起初我认为这是因为返回了一个空值，然后它会尝试匹配导致错误的空值，但是，我重写了我的查询，因此不返回空值并且错误仍然发生。
What would be the best way to rewrite this function, so that the error will not occur
重写此函数的最佳方法是什么，以免发生错误
WITH EmployeeTree AS
(
SELECT
EMP_SRC_ID_NR Id, USR_ACV_DIR_ID_TE Uuid,
CASE Employees.APV_MGR_EMP_ID
WHEN Null THEN '0'
ELSE Employees.APV_MGR_EMP_ID
END as ApprovalManagerId
FROM
dbo.[tEmployees] as Employees WITH (NOLOCK)
WHERE
APV_MGR_EMP_ID = @Id
and Employees.APV_MGR_EMP_ID is not null
and Employees.EMP_SRC_ID_NR is not null
UNION ALL
SELECT
EMP_SRC_ID_NR Id, USR_ACV_DIR_ID_TE Uuid,
CASE Employees.UPS_ACP_EMP_NR
WHEN Null THEN '1'
ELSE Employees.UPS_ACP_EMP_NR
END as ApprovalManagerId
FROM
dbo.[tEmployees] as Employees WITH (NOLOCK)
WHERE
UPS_ACP_EMP_NR = @Id
and Employees.APV_MGR_EMP_ID is not null
and Employees.EMP_SRC_ID_NR is not null
UNION ALL
SELECT
Employees.EMP_SRC_ID_NR, Employees.USR_ACV_DIR_ID_TE,
CASE Employees.APV_MGR_EMP_ID
WHEN Null THEN '2'
ELSE Employees.APV_MGR_EMP_ID
END
FROM
dbo.[tEmployees] as Employees WITH (NOLOCK)
JOIN
EmployeeTree ON Employees.APV_MGR_EMP_ID = EmployeeTree.Id
where
Employees.APV_MGR_EMP_ID is not null
and Employees.EMP_SRC_ID_NR is not null
)
SELECT
Id AS [EmployeeId],
Uuid AS [EmployeeUuid],
ApprovalManagerId AS [ManagerId]
FROM EmployeeTree
回答by Andomar
Specify the maxrecursion optionat the end of the query:
在查询结束时指定maxrecursion 选项：
...
from EmployeeTree
option (maxrecursion 0)
That allows you to specify how often the CTE can recurse before generating an error. Maxrecursion 0 allows infinite recursion.
这允许您指定 CTE 在生成错误之前可以递归的频率。Maxrecursion 0 允许无限递归。
回答by Mou
it is just a sample to avoid max recursion error. we have to use option (maxrecursion 365); or option (maxrecursion 0);
它只是一个避免最大递归错误的示例。我们必须使用选项（maxrecursion 365）；或选项（maxrecursion 0）；
DECLARE @STARTDATE datetime;
DECLARE @EntDt datetime;
set @STARTDATE = '01/01/2009';
set @EntDt = '12/31/2009';
declare @dcnt int;
;with DateList as
(
select @STARTDATE DateValue
union all
select DateValue + 1 from DateList
where DateValue + 1 < convert(VARCHAR(15),@EntDt,101)
)
select count(*) as DayCnt from (
select DateValue,DATENAME(WEEKDAY, DateValue ) as WEEKDAY from DateList
where DATENAME(WEEKDAY, DateValue ) not IN ( 'Saturday','Sunday' )
)a
option (maxrecursion 365);
