带有选项的无限循环CTE（最大递归0）

Question

我有大量记录的CTE查询。以前它工作正常。但是最近，它对某些成员抛出了错误

声明终止。在语句完成之前，最大递归100已用尽。

因此，我提出OPTION (maxrecursion 0)或接受OPTION (maxrecursion 32767)查询，因为我不想限制记录。但是，结果是查询需要永远加载。我该如何解决？

这是我的代码：

with cte as(

-- Anchor member definition

    SELECT  e.SponsorMemberID , e.MemberID, 1 AS Level

    FROM tblMember AS e 

    where e.memberid = @MemberID

union all

-- Recursive member definition

    select child.SponsorMemberID , child.MemberID, Level + 1

    from tblMember child 

join cte parent

on parent.MemberID = child.SponsorMemberID

)

-- Select the CTE result

    Select distinct a.* 

    from cte a

    option (maxrecursion 0)

Answer 1

如果您要达到递归限制，那么您可能在赞助关系或数据循环方面有相当大的深度。如下所示的查询将检测循环并终止递归：declare @tblMember as Table ( MemberId Int, SponsorMemberId Int );insert into @tblMember ( MemberId, SponsorMemberId ) values  ( 1, 2 ), ( 2, 3 ), ( 3, 5 ), ( 4, 5 ), ( 5, 1 ), ( 3, 3 );declare @MemberId as Int = 3;declare @False as Bit = 0, @True as Bit = 1;with Children as (  select MemberId, SponsorMemberId,    Convert( VarChar(4096), '>' + Convert( VarChar(10), MemberId ) + '>' ) as Path, @False as Loop    from @tblMember    where MemberId = @MemberId  union all  select Child.MemberId, Child.SponsorMemberId,    Convert( VarChar(4096), Path + Convert( VarChar(10), Child.MemberId ) + '>' ),    case when CharIndex( '>' + Convert( VarChar(10), Child.MemberId ) + '>', Path ) = 0 then @False else @True end    from @tblMember as Child inner join      Children as Parent on Parent.MemberId = Child.SponsorMemberId    where Parent.Loop = 0 )  select *    from Children    option ( MaxRecursion 0 );

Answer 2

因此问题不是来自maxrecursion。来自CTE。我不知道为什么，但是可能包含任何赞助者周期：A-> B-> C-> A-> ...（