create function [dbo].[Get_SubPositions](@PositionID int)
returns @Subs Table
(
PositionID int not null primary key,
[Level] int not null
)
as
begin
declare @Level as int;
set @Level=0;
--向临时表@Sub中插入根节点
insert into @Subs(PositionID,[Level])
select PositionID, @Level from Position where PositionID=@PositionID
--当存在下级职位时,与临时表链接,设置计数器,筛选直接下级职位,最终临时表中获得所有下级职位
while @@rowcount>0
begin
set @Level=@Level+1; --设置计数器,表示下级下属的级别
insert into @Subs(PositionID,[Level])
select p.PositionID,@Level
from @Subs as s --父级职位
inner join Position p --子级职位
on s.PositionID=p.ParentID
and s.Level=@Level-1 --计算父级职位的级别
and p.PositionID<>p.ParentID
end
return;
end