UFN_SITEHIERARCHY_GETNEWDESCENDANTVALUE

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

Return

Return Type
hierarchyid

Parameters

Parameter	Parameter Type	Mode	Description
@PARENTID	uniqueidentifier	IN

Definition

Copy



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

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

                declare @LASTCHILDHIERARCHYID hierarchyid;

                select
                    @LASTCHILDHIERARCHYID = max(SITE.HIERARCHYPATH)
                from
                    dbo.SITE
                where
                    SITE.HIERARCHYPATH.GetAncestor(1) = @PARENTHIERARCHYID;

                return @PARENTHIERARCHYID.GetDescendant(@LASTCHILDHIERARCHYID, null);
            end