方式一：适用SQL2000+
1 if object_id('sp_GetAllChild')>0
2 drop proc sp_GetAllChild
3 go
4
5 create proc sp_GetAllChild(@pid varchar(20))
6 as
7 begin
8 create table #t(ID int,Parent_ID int,Name varchar(1000),level int)
9 declare @t_id varchar(1000),@l int
10 set @t_id=@pid
11 set @l=0
12 insert into #t select id,parent_id,name ,@l from org_organization where parent_id =@pid or id=@pid
13 while @@rowcount>0
14 begin
15 set @l=@l+1
16 insert #t select a.id,a.parent_id,a.name,@l
17 from org_organization a,#t b
18 where a.parent_Id=b.id and b.level=@l-1
19
20 end
21 select ID,parent_id,name from #t -- where id<>@pid 包括本部门
22
23 end
24
25 --测试：exec sp_GetAllChild '13'
方式二：适用SQL2005+
1 with temp(ID,Parent_ID,Name)
2 as ((select cast(r.ID as integer) as ID,cast(r.Parent_ID AS integer),r.Name
3 from Org_Organization r where cast(r.ID as integer)=1)
4 union all (select cast(cc.ID as integer),cast(cc.Parent_ID as integer),cc.Name
5 from Org_Organization cc,temp p where cast(p.ID as integer) = cast(cc.Parent_ID AS integer) ))
6 select * from temp where 1=1 order by temp.Parent_ID, temp.ID desc