declare @backtrace table(id int,lvl int)
declare @id int
set @id = 756
declare @lvl int
set @lvl=0
insert @backtrace select id, @lvl from tblCallCode where id = @id
while @@rowcount>0
begin
set @lvl=@lvl+1
insert @backtrace select callcode.parentid, @lvl
from [tblcallcode] callcode,@backtrace backtrace
where callcode.id=backtrace.id
and backtrace.lvl=@lvl-1
end
select tblCallCode.* from tblCallCode inner join (select * from @backtrace) t on tblCallCode.id = t.id order by FullPath
declare @id int
set @id = 756
declare @lvl int
set @lvl=0
insert @backtrace select id, @lvl from tblCallCode where id = @id
while @@rowcount>0
begin
set @lvl=@lvl+1
insert @backtrace select callcode.parentid, @lvl
from [tblcallcode] callcode,@backtrace backtrace
where callcode.id=backtrace.id
and backtrace.lvl=@lvl-1
end
select tblCallCode.* from tblCallCode inner join (select * from @backtrace) t on tblCallCode.id = t.id order by FullPath