UFN_CAMPAIGN_GETHIERARCHYPATHINSERTPOSITION

This function returns the next available descendant hierarchyid for a campaign.

Return

Return Type
hierarchyid

Parameters

Parameter	Parameter Type	Mode	Description
@PARENTID	uniqueidentifier	IN

Definition

Copy



            create function dbo.UFN_CAMPAIGN_GETHIERARCHYPATHINSERTPOSITION (
                @PARENTID uniqueidentifier = null
            ) returns hierarchyid
            as begin
                declare @PARENTHIERARCHYID hierarchyid;

                if @PARENTID is null
                    set @PARENTHIERARCHYID = hierarchyid::GetRoot();
                else begin
                    select
                        @PARENTHIERARCHYID = [CAMPAIGN].[HIERARCHYPATH]
                    from
                        dbo.[CAMPAIGN]
                    where
                        [CAMPAIGN].ID = @PARENTID;
                end

                declare @LASTCHILDHIERARCHYID hierarchyid;
                select
                        @LASTCHILDHIERARCHYID = max([CAMPAIGN].[HIERARCHYPATH])
                    from
                        dbo.[CAMPAIGN]
                    where
                        [CAMPAIGN].[HIERARCHYPATH].GetAncestor(1) = @PARENTHIERARCHYID;

                declare @INSERTPOSITION hierarchyid;        
                set @INSERTPOSITION = @PARENTHIERARCHYID.GetDescendant(@LASTCHILDHIERARCHYID, null);

                return @INSERTPOSITION;    
            end