Click here to Skip to main content
15,867,330 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
0


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.


What I have tried:

i try some changes and search everywehe but i cant find answer
Posted
Updated 19-Nov-22 21:20pm

1 solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900