下面用递归方法在sql server中获取所有部门:
CREATE FUNCTION [dbo].[Func_GetDeptName]
(
-- 当前部门的上级部门ID
@deptPid int,
-- 当前部门的名称
@deptName varchar(1000),
-- 是否取到根部门
@isRootName int
)
RETURNS varchar(1000)
AS
BEGIN
declare @temp varchar(50)
declare @id int
set @id = 0
select @id=DEPARTMENT_PID,@temp=DEPARTMENT_Name from DEPARTMENT where DEPARTMENT_ID=@deptPid
if 0 = @id and 1=@isRootName and 0 <> @deptPid
set @deptName = @temp +' > '+ @deptName;
else if @id<>0
set @deptName = dbo.Func_GetDeptName(@id,@temp,@isRootName)+' > '+ @deptName
-- Return the result of the function
RETURN @deptName;
END
下面是调用function
select *,dbo.Func_GetDeptName(DEPARTMENT_PID,DEPARTMENT_NAME,1) as DeptFullName from DEPARTMENT d