UFN_EVENT_GETHIERARCHYPATHINSERTPOSITION

This function returns the next available descendant hierarchyid for an event.

Return

Return Type
hierarchyid

Parameters

Parameter	Parameter Type	Mode	Description
@PARENTID	uniqueidentifier	IN

Definition

Copy



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

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

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

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

                return @INSERTPOSITION;    
            end