BOM之查询指定节点及其所有子节点

查询组织下的所有组织，EWF_SYS_Groups 是组织表，GroupCD 组织编号，GroupName组织名，GroupLevel组织层级

sql函数

create function fn_getallsubgroup(
    @groupcd nvarchar(50)
)
returns @AllSubGroup Table(GroupCD nvarchar(50),GroupName nvarchar(50),GroupLevel int)
as
begin
    declare @grouplevel int=0
    declare @groupname nvarchar(50)=''
    --查询当前组织名和组织层级
    select @grouplevel=GroupLevel,@groupname=GroupName From EWF_SYS_Groups where GroupCD=@groupcd
    insert into @AllSubGroup select @groupcd,@groupname,@grouplevel 
    while @@RowCount>0
    begin
      set @grouplevel=@grouplevel+1
      insert into @AllSubGroup select a.GroupCD,a.GroupName,@grouplevel from EWF_SYS_Groups a
        inner join @AllSubGroup b on a.GroupParentCD=b.GroupCD and b.GroupLevel=@grouplevel-1
    end
    return
end