MSSql2008 递归获取等级结构

create procedure GetOfficeIdLevel
@type int,
@officeid int
as


if @type = 1   ---查询上级结构
begin


with cte_parent(OFFICE_ID,office_native_name,SUPERIOR_OFFICE_ID)
as 
( 
 
select OFFICE_ID,office_native_name,SUPERIOR_OFFICE_ID
 
from SB_DEPARTMENT
 
where office_id = @officeid   
 
union all
 
--递归条件
 
select a.OFFICE_ID,a.office_native_name,a.SUPERIOR_OFFICE_ID
 
from SB_DEPARTMENT a
 
inner join 
 
cte_parent b         
 
on a.office_id=b.SUPERIOR_OFFICE_ID  
 
)                       
 
select * from cte_parent;
end
---------------------------------查询下级结构
else
begin


with cte_child(OFFICE_ID,office_native_name,SUPERIOR_OFFICE_ID,rlevel)
 
as
 
(
 
--起始条件
 
select OFFICE_ID,office_native_name,SUPERIOR_OFFICE_ID,0 as rlevel
 
from SB_DEPARTMENT
 
where OFFICE_ID = @officeid
 
union all
 
--递归条件
 
select a.OFFICE_ID,a.office_native_name,a.SUPERIOR_OFFICE_ID,b.rlevel+1
 
from SB_DEPARTMENT a
 
inner join 
 
cte_child b
 
on ( a.SUPERIOR_OFFICE_ID=b.OFFICE_ID)  
 
) 
select  * from cte_child;
end


go