UFN_GETCHILDREN_FOR_LEVEL3_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_LEVEL3_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 not null and parent.DESIGNATIONLEVEL2ID = child.DESIGNATIONLEVEL2ID
                    and parent.DESIGNATIONLEVEL3ID is not null and parent.DESIGNATIONLEVEL3ID = child.DESIGNATIONLEVEL3ID
                    and parent.DESIGNATIONLEVEL4ID is null and child.DESIGNATIONLEVEL4ID is not null)

                return
            end