SQL Server : Recursive CTE (Hierarchy Example)

create table #temp
(
id int,
name varchar(30),
parent_id int
)

insert #temp ( id, name, parent_id )
values  ( 1, 'A', null )
, ( 2, 'B', null )
, ( 3, 'C', null )
, ( 4, 'AA', 1 )
, ( 5, 'BB', 2 )
, ( 6, 'AAA', 4 )
, ( 7, 'AAAA', 6 )

select * from #temp

image

;with cte (id,name,parent)
as
(
select id, name,cast('' as varchar(30)) as parent
from #temp
where parent_id is null

union all

select c1.id,c1.name,c2.name as parent
from #temp c1
join cte c2 on c1.parent_id = c2.id
)


select id,name,parent from cte

image

Advertisements

SQL Server : Recursive CTE and Maximum Recursion

Default maximum recursion is 100. This can be altered by setting the maxrecursion as an option. If the recursion has gone past the maximum then you will receive the following error:

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

Here is an example with maximum recursion set to 1000.

;with cte(N) as
(
select 1 as N
union all
select cte.N + 1
from cte
where cte.N <1000
)
select * from cte
option (maxrecursion 1000)