在语句完成之前，递归100的最大值已经用尽

我不断得到一个 max recursion error与这个查询。

起初我认为这是因为返回了一个空值，然后它会尝试匹配空值导致错误，但是，我重写了我的查询，所以不返回空值，错误仍然发生。

重写这个函数的最佳方法是什么，这样就不会发生错误

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

245586

小开

这只是一个避免最大递归误差的例子，我们必须使用 选项(最大递归365) ; 或选项(最大递归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);