Create recursive CTE for this table

Question

I have a table like this:

|id     |name      |parent|
+-------+----------+------+
|1      |iran      |      |
|2      |iraq      |      |
|3      |tehran    |1     |
|4      |tehran    |3     |
|5      |Vaiasr St |4     |
|6      |Fars      |1     |
|7      |shiraz    |6     |

It's about addresses from country to street. I want to create address by recursive cte like this:

with cte_address as
(
    select 
        ID, [Name], parent
    from 
        [Address]
    where 
        Parent is null
    union all
    select
        a.ID, a.[name], a.Parent
    from 
        address a
    inner join 
        cte_address c on a.parent = c.id
)
select *  
from cte_address

But I get an error:

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Can't reproduce with the supplied data. Probably your actual data has a cycle in it, such as 8->8, or 9->8->9. — AlwaysLearning, Nov 20 '22 at 07:45

masoud rafiee · Accepted Answer · 2022-11-20T08:19:04.680

1

you have to use option (maxrecursion 0) at the end of your select query,Maxrecursion 0 allows infinite recursion:

with cte_address as
(
...
...
)
 select * from cte_address 
 option (maxrecursion 0)

Note : Limiting the number of recursions allowed for a specific query in SQL Server with the 100 default value prevents the cause of an infinite loop situation due to a poorly designed recursive CTE query.

edited Nov 20 '22 at 08:19

answered Nov 20 '22 at 08:13

masoud rafiee

427
3
9

Create recursive CTE for this table

1 Answers1