转自：http://topic.csdn.net/t/20021105/08/1148751.html
我做的一个树的查询，该出一个id，获得它所有子节点，
按父节点后跟子节点层次排列，应该可以满足你的使用。
create procedure tree_search
(
@id int
)
AS
declare @level int, @cur int
create table #stack
(
depth_level int,
tbl_id int,
)
create table #tmp
(
seq_no int identity,
org_level int not null,
tbl_id int not null
)
select @level=1
select @cur=@id
--need change in terms of case
insert into #stack(depth_level,tbl_id) select @level,id from yourTable where id=@id
--end ------------------------
while (@level> 0)
begin
if exists (select * from #stack where depth_level=@level)
begin
set ROWCOUNT 1
select @cur=tbl_id from #stack where depth_level=@level
set ROWCOUNT 0
insert into #tmp(org_level,tbl_id) select @level,@cur
delete from #stack where depth_level=@level and tbl_id=@cur
--need change in terms of case
insert into #stack select @level+1,id from yourTable where parent_id=@cur and parent_id <> id
--end ------------------------
if @@ROWCOUNT> 0
select @level=@level+1
end
else
begin
select @level=@level-1
end
end
select * from #tmp
