UFN_GETCHILDREN_FOR_LEVEL1_DESIGNATION

get the designations that exist at the next level below a given designation in the hierarchy

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode	Description
@designationId	uniqueidentifier	IN

Definition

Copy



            create function dbo.UFN_GETCHILDREN_FOR_LEVEL1_DESIGNATION
            (
                @designationId uniqueidentifier
            )
            returns @GuidsTable table(designationid uniqueidentifier)
            as

            begin
                insert into @GuidsTable
                    select child.ID from DESIGNATION parent cross join DESIGNATION child where parent.ID = @designationId
                    and (parent.Designationlevel1ID = child.DESIGNATIONLEVEL1ID
                    and parent.DESIGNATIONLEVEL2ID is null and child.DESIGNATIONLEVEL2ID is not null)

                return
            end