--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