有关树操作的sql存储过程，帮忙啊

[jarryandtom]

根据ID查询处子树 的存储过程。
树结构
id,parentid,sname
1 0 aaaa
2 0 bbbb
3 1 a1
4 1 a2
5 3 a11
6 3 a12

id=1
查询出来的结果是：
id,parentid,sname
1 0 aaaa
3 1 a1
4 1 a2
5 3 a11
6 3 a12

这样的存储过程怎么写啊
大虾帮忙啊

[jarryandtom]

在先等。。。。。。。。。

[Yang_]

create proc pr_q_tree
@id int
as

declare @t table (
id int,
parentid int,
sname varchar(30),
lev int
)

declare @lev int
set @lev=0
insert @t select *,@lev from 树结构表 where id=@id

while exists ( select 1 from 树结构表 where parentid in (select id from @t where lev=@lev)
)
begin
insert @t select *,@lev+1 from 树结构表 where parentid in (select id from @t where lev=@lev)
set @lev=@Lev+1
end

select id,parentid,sname
from @t

go

--调用
exec pr_q_tree 1

[lwl0606]

create table tt (id int ,parentid int ,sname nvarchar(20))
insert into tt
select 1 , 0 , 'aaaa'
union select 2 , 0, 'bbbb'
union select 3 , 1, 'a1'
union select 4 , 1, 'a2'
union select 5 , 3, 'a11'
union select 6, 3, 'a12'



CREATE PROCEDURE dbo.tree
@id nvarchar(10)
AS
begin
create table #tt (id int ,parentid int ,sname nvarchar(20))
insert into #tt select * from tt where id =@Id
while @@rowcount<>0
begin
insert into #tt select * from tt a where parentid in (select id from #tt) and not exists(select 1 from #tt b where a.id=b.id and a.parentid=b.parentid )
end
select * from #tt
end
GO

[lwl0606]

exec tree 1

--result
ID parentid sname
1 0 aaaa
3 1 a1
4 1 a2
5 3 a11
6 3 a12

[jarryandtom]

2位，一定要用临时表么？

[lwl0606]

不用临时表不好解决，深度不好判断，你不是要存储过程嘛？临时表是在存储过程内部，存储过程执行完就自动删除里面的临时表了，不影响