表的结构如图:
--根据传入的部门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)