sql根据子部门id动态获取部门全路径
1.调用列子:select *From [dbo].[f_GetParentFullName](981769)
2.创建函数
create FUNCTION [dbo].[f_GetParentFullName](@id int)
RETURNS @str TABLE(FullNam varchar(1000))
AS
begin
declare @level int
declare @FullName varchar(1000)
set @level = 1
declare @pid int
select @pid = FPARENTID from T_BD_DEPARTMENT where FDEPTID = @id
select @FullName=t1.FNAME from T_BD_DEPARTMENT t inner join T_BD_DEPARTMENT_L t1 on t.FDEPTID=t1.FDEPTID and t1.FLOCALEID=2052 where t.FDEPTID = @id
select @pid = FPARENTID from T_BD_DEPARTMENT where FDEPTID = @id
select @FullName=t1.FNAME+'/'+@FullName from T_BD_DEPARTMENT t inner join T_BD_DEPARTMENT_L t1 on t.FDEPTID=t1.FDEPTID and t1.FLOCALEID=2052 where t.FDEPTID = @pid
while @@rowcount > 0
begin
select @pid = FPARENTID from T_BD_DEPARTMENT where FDEPTID = @pid
select @FullName=t1.FNAME+'/'+@FullName from T_BD_DEPARTMENT t inner join T_BD_DEPARTMENT_L t1 on t.FDEPTID=t1.FDEPTID and t1.FLOCALEID=2052 where t.FDEPTID = @pid
end
insert @str select @FullName
return
end
GO
sql根据子部门id动态获取部门全路径
本文2024-09-16 19:06:32发表“云星空知识”栏目。
本文链接:https://wenku.my7c.com/article/kingdee-k3cloud-26452.html