语句完成之前已用尽最大递归100 [英] The maximum recursion 100 has been exhausted before statement completion
本文介绍了语句完成之前已用尽最大递归100的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
此查询不断出现 最大递归错误
。
I keep getting a max recursion error
with this query.
在首先,我认为这是因为返回了null,然后它将尝试匹配导致错误的null值,但是,我重新编写了查询,以便不返回null并仍然发生错误。
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
推荐答案
指定 maxrecursion选项在查询末尾:
...
from EmployeeTree
option (maxrecursion 0)
这使您可以指定CTE在产生错误之前可以递归的频率。 Maxrecursion 0允许无限递归。
That allows you to specify how often the CTE can recurse before generating an error. Maxrecursion 0 allows infinite recursion.
这篇关于语句完成之前已用尽最大递归100的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文