UFN_SPONSORSHIPLOCATION_GETCMSPARENTID

create function dbo.UFN_SPONSORSHIPLOCATION_GETCMSPARENTID(@ID uniqueidentifier) 
returns uniqueidentifier
with execute as caller
as begin
    declare @PARENTID uniqueidentifier;
    declare @DISPLAYONLINE int;
    declare @HIERARCHYPATHID hierarchyid;

    select @HIERARCHYPATHID = HIERARCHYPATH,
             @DISPLAYONLINE = DISPLAYONLINE
    from dbo.SPONSORSHIPLOCATION where ID = @ID;

    if @DISPLAYONLINE = 0
    begin
        select @PARENTID = ID,
                 @DISPLAYONLINE = DISPLAYONLINE
        from dbo.SPONSORSHIPLOCATION
        where HIERARCHYPATH = @HIERARCHYPATHID.GetAncestor(1);

        if @DISPLAYONLINE = 1 or @PARENTID is null -- when at top and no records found.

            return @PARENTID;
        else
            return dbo.UFN_SPONSORSHIPLOCATION_GETCMSPARENTID(@PARENTID);
    end

    return @ID;
end