获取所有子部门函数

--COODING BY JEREMY
--2010-08-08
--Get all sub Department : SELECT [Get_SubDepartment]( x)
--Get Employee : SELECT * FROM Employee JOIN  [Get_SubDepartment]( x)  ON depid=DeptId

CREATE    FUNCTION [dbo].[Get_SubDept]
(
   @DeptID varchar(50) --输入参数，要查询了部门的父部门编号
)
RETURNS
@Dept TABLE    --返回所有子部门组成的一个临时表
(
deptid [varchar] (50),
deptname [varchar] (100),
si int
)
AS
BEGIN

declare @LoopCounter INT
select @LoopCounter=0

--保留当前组织本身
insert into @Dept(deptid,deptname,si)
select TableKey,部门名称,0 from 字典_部门 where TableKey=@DeptID
while @@rowcount>0
begin
     select @LoopCounter=@LoopCounter+1
     insert into @Dept(deptid,deptname,si )
     select TableKey,部门名称,@LoopCounter from 字典_部门 join @Dept on deptid=父部门id
     where  si=@LoopCounter-1
end
return
END