UFN_DESIGNATIONS_GETCHILDREN

            CREATE function [dbo].[UFN_DESIGNATIONS_GETCHILDREN] 
            (
                @parentIDsList nvarchar(2000)
            )

            returns @GuidTable table(designationid uniqueidentifier)
            as
            begin

            declare @level int
            declare @designationGUIDsTable table (id uniqueidentifier)
            declare @guidTempTable table (id uniqueidentifier)
            declare @curID uniqueidentifier

            set @level = -1

            insert into @designationGUIDsTable
                        select g from dbo.fnMakeGuidsTableFromString(@parentIDsList, ',')

            --use a cursor to scroll through the passed in designations and select each of their children designation ids        

            declare myCur cursor local fast_forward for
                select id from @designationGUIDsTable

            open myCur
            fetch next from myCur into @curID 
            while @@fetch_status = 0

                begin

                    --figure out what level the designation exists in it's hierarchy        

                    select @level = 1
                    where exists (
                        select '' from DESIGNATION 
                        where ID = @curID and DESIGNATIONLEVEL1ID is not null and DESIGNATIONLEVEL2ID is null)

                    if @level = -1
                    begin
                        select @level = 2
                        where exists (
                            select '' from DESIGNATION 
                            where ID = @curID and DESIGNATIONLEVEL1ID is not null and DESIGNATIONLEVEL2ID is not null 
                            and DESIGNATIONLEVEL3ID is null)
                    end

                    if @level = -1
                    begin
                        select @level = 3
                        where exists (
                            select '' from DESIGNATION 
                            where ID = @curID and DESIGNATIONLEVEL1ID is not null and DESIGNATIONLEVEL2ID is not null 
                            and DESIGNATIONLEVEL3ID is not null and DESIGNATIONLEVEL4ID is null)
                    end

                    if @level = -1
                    begin
                            select @level = 4
                            where exists (
                                select '' from DESIGNATION 
                                where ID = @curID and DESIGNATIONLEVEL1ID is not null and DESIGNATIONLEVEL2ID is not null 
                                and DESIGNATIONLEVEL3ID is not null and DESIGNATIONLEVEL4ID is not null and DESIGNATIONLEVEL5ID is null)
                    end

                    if @level = -1
                    begin
                        select @level = 5
                        where exists (
                            select '' from DESIGNATION 
                            where ID = @curID and DESIGNATIONLEVEL5ID is not null)
                    end

                    if @level = 1 
                        insert into @guidTempTable
                            select designationid from dbo.UFN_GETCHILDREN_FOR_LEVEL1_DESIGNATION(@curID)
                    if @level = 2
                        insert into @guidTempTable
                            select designationid from dbo.UFN_GETCHILDREN_FOR_LEVEL2_DESIGNATION(@curID)
                    if @level = 3
                        insert into @guidTempTable
                            select designationid from dbo.UFN_GETCHILDREN_FOR_LEVEL3_DESIGNATION(@curID)
                    If @level = 4
                        insert into @guidTempTable
                            select designationid from dbo.UFN_GETCHILDREN_FOR_LEVEL4_DESIGNATION(@curID)
                    if @level = 5
                    --do nothing because i'm the last level down and I have no children


                    --reset @level before you move to next designation in cursor

                    set @level = -1 

                    fetch next from myCur into @curID
                end

            close myCur
            deallocate myCur

            insert into @GuidTable
                select distinct * from @guidTempTable 
                union 
                select * from @designationGUIDsTable

            return
        end