UFN_CORPORATION_GETSUBSIDIARIES

create function dbo.UFN_CORPORATION_GETSUBSIDIARIES
(
    @ORGANIZATIONID uniqueidentifier,
    @INCLUDEROOT bit = 0
) 
returns @SUBSIDIARIES table (ID uniqueidentifier, DEPTH integer)    
begin

    declare @LOOPLIMIT integer = 255;
    declare @LOOPCOUNTER integer = 0;

    insert into @SUBSIDIARIES
        (ID, DEPTH)
    values 
        (@ORGANIZATIONID, 0);

    set @LOOPCOUNTER = 0
    while (select count(ID) from dbo.ORGANIZATIONDATA where PARENTCORPID in (select ID from @SUBSIDIARIES where DEPTH = @LOOPCOUNTER)) > 0 and @LOOPCOUNTER < @LOOPLIMIT
        begin
            insert into @SUBSIDIARIES
                (ID, DEPTH) 
            select 
                ORGANIZATIONDATA.ID,
                @LOOPCOUNTER + 1
            from dbo.ORGANIZATIONDATA            

            where ORGANIZATIONDATA.PARENTCORPID in (select ID from @SUBSIDIARIES where DEPTH = @LOOPCOUNTER)
                and not exists(select top 1 ID from @SUBSIDIARIES s where s.ID = ORGANIZATIONDATA.ID )

            set @LOOPCOUNTER = @LOOPCOUNTER + 1
        end


        if @INCLUDEROOT = 0
            delete @SUBSIDIARIES
            where ID = @ORGANIZATIONID;

    return;

end