使用存储过程处理树

table:tree
id  content parentid
1    1    0
2    2    0
3    3    0
4    11    1
5    12    1
6    31    3
7    111    1
8    4    0
9    41    8


proc: ds_tree
ALTER     proc ds_tree
(@parent int=-1,@ls_bh varchar(100)='0')
as
declare @i int,@n int
declare @ls_bh1 varchar(100)
if @parent=-1   --调用时建立临时表，不要传初值
begin
    create table #tmp(orderbh varchar(100),id1 int,name1 varchar(10),level int)
        set @parent=0
end

--**********************************************************************
insert into #tmp(id1,name1,level)                                 --插入下层子结点
       select id,content,@@nestlevel
          from tree where parentid=@parent
set @i=0
update  #tmp set orderbh=@ls_bh+'_'+cast(@i as varchar(2)),@i=@i+1 where orderbh is null

--*************************************************************************
set @n=@@rowcount


if @n=0
begin
      return                  --递归出口
end

set  @i=1

while @i<=@n
begin 

    set @ls_bh1=@ls_bh+'_'+cast(@i as varchar(2))
    select @parent=id1 from #tmp where orderbh=@ls_bh1

        exec ds_tree @parent,@ls_bh1
        set @i=@i+1  
end

if @@NESTLEVEL=1
        begin
           select id1,name1,level from #tmp order by orderbh       
        end


GO
 应用程序调用:简单描述(根－子)