获得某一部门下的所有子部门

表的结构如图：

--根据传入的部门ID，返回它的所有子部门 @cid企业编号

alter function fn_GetDeptID(@DeptId int,@cid int)

    returns  @tb table (id int)

as 

begin

    insert into @tb

    select DeptID from DepartmentInfo where CorpID=@cid and (ParentID = @DeptId or DeptID=@DeptId)  

    while @@Rowcount > 0 

    begin

        insert into @tb

        select DeptID from DepartmentInfo as a 

		inner join @tb as b on a.ParentID = b.id and a.DeptID not in(select id from @tb)

    end

    return

end

go 



select * from DepartmentInfo where DeptID in 

(select * from dbo.fn_GetDeptID(3,10001)

)



--根据传入的部门ID，返回它的上级部门

create function fn_GetParentID(@DeptId int )

    returns  @tb table (id int)

as 

begin

    insert into @tb

    select ParentID from DepartmentInfo where DeptID = @deptid

    while @@Rowcount >0 

    begin

        insert into @tb

        select ParentID 

            from DepartmentInfo as a inner join @tb as b on a.DeptID = b.id and a.ParentID not in(select id from @tb)

    end

    return

end

go 



select * from dbo.fn_GetParentID(12)