根据某个部门ID递归获取其下面所有子部门和本部门信息

方式一：适用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

转载于:https://www.cnblogs.com/252e/archive/2011/02/22/1961479.html