MSSQL查找所有子节点

核心提示：beginset @lev=@lev+1insert into @t select a.*,@lev from os a,@t bwhere a.parentid=b.id and b.lev=@lev-1enddeclare @cids varchar(500)select @cids=isnull(@cids+&#

begin
set @lev=@lev+1
insert into @t select a.*,@lev from os a,@t b
where a.parentid=b.id and b.lev=@lev-1
end
declare @cids varchar(500)
select @cids=isnull(@cids+',','')+ltrim(id) from @t order by lev
return @cids
end
go

--调用函数
select *,ids=dbo.f_cid(id) from os

--得到每个节点路径：
create proc wsp2
@id int
as
select *,cast(' ' as varchar(10)) fullpath into #os from os
DECLARE @i int,@j int
set @i=0
set @j=1
select @i=max(parentid) from #os
update #os set fullpath=id
while @j<=@i
begin
update #os set fullpath=a.fullpath+','+ltrim(#os.id)
from #os inner join #os a on #os.parentid=a.id
where #os.parentid=@j
set @j=@j+1
end
select * from #os
go
--调用存储过程
exec wsp2 1