根据根节点获取所有的子节点SQL语句

CREATE function [dbo].[GetChildOrgID](@OrgID varchar(36))   
returns @t table(OrgID varchar(36),ParentOrgID varchar(36),Level int)   
as  
begin  
    declare @i int  
    set @i = 1   
    insert into @t select @OrgID,@OrgID,0 --当前级，本级，如果不要的话可以注释掉或再加个参数来选择操作   
    insert into @t select OrgID,ParentOrgID,@i from SM_Organization where ParentOrgID = @OrgID   
  
    while @@rowcount<>0   
    begin  
        set @i = @i + 1   
        insert into @t   
        select  
            a.OrgID,a.ParentOrgID,@i   
        from  
            SM_Organization a,@t b   
        where  
            a.ParentOrgID=b.OrgID and a.Status !='1' and b.Level = @i-1   
    end  
    return  
end
GO

转载于:https://www.cnblogs.com/Jack_G/archive/2011/11/29/2267639.html