i did some research and got the following quries

--bottom to top

;WITH

CTE_Search AS

(

SELECT

...

can some one here please help me?

thanks lynn,

following is what i want..

2323 (2323 should have childs like 23231000,23232,23233000,232390 as its child))

23231000 ( should have nothing)

23232 (should have 232321,232329 as its child)

23233000(should have 23239010,23239020,23239030,23239040,23239090 as its child)

232390...

can anyone heree pls help me?????????

insert into test222 values (1, '11', 0, 'iam a boy')

insert into test222 values (2, '111', 1, 'boy')

insert into test222 values (3, '1111', 2, 'boo')

insert into test222 values (4, '11111', 2,...

sorry for my mistake, let us consider this...

insert into test222 values (1, '11', 0, 'a')

insert into test222 values (2, '111', 1, 'ccc')

insert into test222 values (3, '1111', 2, 'c')

insert into...

WITH cteSubs AS (

SELECT sid, scode, ParentID, sname, 0 Level, CAST(N'/' + scode + N'/' AS NVarchar(MAX)) Path

...

thanks i did what you said,,, and following is what i am getting

21111ccc1/11/111/

311112c2/11/111/1111/

51111114c4/11/111/1111/11111/111111/

i just want ; my expected op is as follows..pls help me

21111ccc1/11/111/

51111114c4/11/111/1111/11111/111111/

thanks, but can you please correct my query ??? i have created my query as a stored procedure so if you could help me to correct it , it will...

thank you very much, but one small change if search C or any value between ,,its not returning anything.

for example if i search c i should get a-b-c

if i search...

can someone here please help me???

yes it is consistent...

i tried substring its not giving the expected out in differnt columns..

YES, thats what i am going to dp...

ok thanks,

even if i retrieve single record also its taking around 8 seconds so that means something is going wrong with my query..

pls let me know what can i do...

no i dont know about the correct indexes , how can i find that out?