Recursive CTE Sample

  


 create table test222(sid int, scode nvarchar(50), parentid int, sname nvarchar(50))


insert into test222 values (1, '2323',         0, 'iam a boy')
insert into test222 values (2, '23231000',     1, 'boy')
insert into test222 values (3, '23232',     1, 'boo')
insert into test222 values (4, '232321',     3, 'bo')
insert into test222 values (5, '23232110',     4, 'boyy')
insert into test222 values (6, '23232190',     4, 'gril')
insert into test222 values (7, '232329',     3, 'body')
insert into test222 values (8, '23232910',     7, 'girll')
insert into test222 values (9, '23232990',     7, 'boy')
insert into test222 values (10, '23233000',     1, 'bo')
insert into test222 values (11, '232390',     1, 'nh')
insert into test222 values (12, '23239010',     10, 'ui')
insert into test222 values (13, '23239020',     10, 'dert')
insert into test222 values (14, '23239030',     10, 'hyui')
insert into test222 values (15, '23239040',     10, 'nji')
insert into test222 values (16, '23239090',     10, 'vfr')
insert into test222 values (17, '2345',     0, 'boo')
insert into test222 values (18, '23455',     17, 'bo')

declare @name  nvarchar(50) ='boy'

declare @tmp table
(
  sid int  primary key, Parentid int, scode nvarchar(50),  sname nvarchar(50))

;with mycte as (
select sid, scode,  Parentid, sName,  Cast(scode as varchar(2000)) as Hierarchy, 1 as lvl from test222 WHERE sname =@name
union all
select t.sid,t.scode, t.Parentid, t.sName,  Cast(t.scode+'\'+m.Hierarchy as varchar(2000)) , m.lvl+1 from test222 t join mycte m on t.sid=m.Parentid

)

 insert into @tmp (sid, scode,  Parentid, sName)
 Select distinct sid, scode,  Parentid, sName  FROM mycte

;WIth resultCTE as (

select sid, scode,  Parentid, sName,  Cast(scode as varchar(2000)) as Hierarchy, 1 as Level  from @tmp Where parentid=0
Union all
select m1.sid, m1.scode,  m1.Parentid, m1.sName,  Cast(m1.scode+'\'+m2.Hierarchy as varchar(2000)), m2.Level+1  
from @tmp m1 join resultCTE m2 on m1.Parentid=m2.sid
)
Select * from resultCTE

 



drop table test222
 

http://stackoverflow.com/questions/10095032/order-of-recursion-sql-server-cte
http://forums.asp.net/t/2054555.aspx?query