UFN_SPONSORSHIPLOCATION_GETNEWDESCENDANTVALUE

Returns the insert point for a new sponsorship location underneath the specified sponsorship location.

Return

Return Type
hierarchyid

Parameters

Parameter	Parameter Type	Mode	Description
@PARENTID	uniqueidentifier	IN

Definition

Copy



create function dbo.UFN_SPONSORSHIPLOCATION_GETNEWDESCENDANTVALUE(
    @PARENTID uniqueidentifier = null
)
returns hierarchyid
with execute as caller
as begin
    declare @PARENTHIERARCHYID hierarchyid;

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

    declare @LASTCHILDHIERARCHYID hierarchyid;

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

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

    return @INSERTPOSITION;    
end