SQL SERVER CTE - infinite recursivity

Question

I have 2 tables:

PLAN_Type_FIRST_ETAP_Type:

PLAN_Type_Id    |   ETAP_TypeFirst_Id
----------------+---------------------
      1         |       5
      2         |       9
      3         |       8

ETAP_TypeTransition:

PLAN_Type_Id    |   ETAP_Type_Id    |   ETAP_TypeNext_Id
----------------+-------------------+----------------------
      2         |       6           |       7
      3         |       8           |       9
      2         |       7           |       10
      2         |       11          |      NULL
      3         |       9           |       8
      2         |       10          |       11

For a PLAN_Type_Id there's a ETAP_TypeFirst_Id, then we find it in the other table, then we take the ETAP_TypeNext_Id then we take the ETAP_Type_Id that is the same of the ETAP_Type_Id.

So we have etapes that are following each other for a plan.

So to order this i have made that :

WITH CTEA (id_plan, id_firstetp)
AS
(
    SELECT distinct p.PLAN_Type_Id as id_plan, p.ETAP_TypeFirst_Id as id_firstetp 
    from PLAN_Type_FIRST_ETAP_Type p 
    where p.ETAP_TypeFirst_Id is not null
),
cte (id_plan,id_firstetp, etp_next)
AS     (SELECT c.id_plan, et.ETAP_Type_Id, et.ETAP_TypeNext_Id as etp_next
        from ETAP_TypeTransition as et inner join CTEA as c
        on c.id_plan=et.PLAN_Type_Id and c.id_firstetp=et.ETAP_Type_Id
        UNION ALL
        SELECT et.PLAN_Type_Id, et.ETAP_Type_Id, et.ETAP_TypeNext_Id
        FROM   cte inner join ETAP_TypeTransition et
        on etp_next=et.ETAP_Type_Id and et.PLAN_Type_Id=id_plan
       )
       select * from cte order by id_plan

The problem is because of the rows

PLAN_Type_Id    |   ETAP_Type_Id    |   ETAP_TypeNext_Id
----------------+-------------------+----------------------
      3         |       8           |       9
      3         |       9           |       8

I have the message:

L'instruction a été terminée. La récursivité maximale 100 a été épuisée avant la fin de l'instruction.

I have made that because my goal is to select in this example values : 3 ---- 8 ---- 9, which produce a loop. There can be several loops (but just one by plan).

Expected output

3 ---- 8 ---- 9

How can i achieve that ?

Use OPTION(MAXRECURSION 0) Refer Link, [http://stackoverflow.com/questions/15080922](http://stackoverflow.com/questions/15080922) — SHD, May 11 '17 at 11:38
What are you trying to do? What is the actual problem you want to solve *in English*? Are you trying to walk a hierarchy or a graph? For example, SQL Server offers the `hierachyid` type to specify hierarchies. You don't need recursion, just a function to return all children of a specific node. Walking a graph can be a lot easier if you use an appropriate schema or query. — Panagiotis Kanavos, May 11 '17 at 11:41
the prolem to solve is to output the line (3--8--9) wich is the start of the loop. — hedidev1, May 11 '17 at 11:44
I understand that your data as it is in the table has a loop. Does that data accurately represent the real world phenomenon it's trying to model? That is, if this is "what desk does this piece of paper go to after being processed?" that process will also have a loop (i.e. in my example, that piece of paper will go between desks 8 & 9 forever). — Ben Thul, May 11 '17 at 14:59
A normal case is : 2-3 > 3-5 > 5-Null (Null means end, and there s no loop). A not normal case is (more globally than 8-9 > 9-8) : 2-3 > 3-5 > 5-6 > 6-2, so we finally go back to the begining. Thanks — hedidev1, May 11 '17 at 15:08

score 0 · Answer 1 · answered May 11 '17 at 11:39

WITH CTEA (id_plan, id_firstetp)
AS
(
SELECT distinct p.PLAN_Type_Id as id_plan, p.ETAP_TypeFirst_Id as id_firstetp from PLAN_Type_FIRST_ETAP_Type p where p.ETAP_TypeFirst_Id is not null
),
            cte (id_plan,id_firstetp, etp_next)
AS     (SELECT c.id_plan, et.ETAP_Type_Id, et.ETAP_TypeNext_Id as etp_next from ETAP_TypeTransition as et inner join CTEA as c
        on c.id_plan=et.PLAN_Type_Id and c.id_firstetp=et.ETAP_Type_Id
        UNION ALL
        SELECT et.PLAN_Type_Id, et.ETAP_Type_Id, et.ETAP_TypeNext_Id
        FROM   cte inner join ETAP_TypeTransition et
        on etp_next=et.ETAP_Type_Id and et.PLAN_Type_Id=id_plan
       )
       select * from cte order by id_plan
       option (maxrecursion 0)

SQL SERVER CTE - infinite recursivity

1 Answers1