UFN_SPONSORSHIP_GETDESIGNATIONBYLOCATION

create function dbo.UFN_SPONSORSHIP_GETDESIGNATIONBYLOCATION(
@LOCATIONID uniqueidentifier = null
)
returns uniqueidentifier
with execute as caller
as begin

  declare @DESIGNATIONID uniqueidentifier;
  declare @HIERARCHYID hierarchyid;
  declare @LEVEL int;

  if @LOCATIONID is null
      return null

  select 
        @DESIGNATIONID = DESIGNATIONID,
        @HIERARCHYID = HIERARCHYPATH.GetAncestor(1),
        @LEVEL = HIERARCHYPATH.GetLevel()
  from SPONSORSHIPLOCATION 
  where @LOCATIONID= ID

  if @DESIGNATIONID is not null
     return @DESIGNATIONID
  else
    if @LEVEL > 1
      begin
        select @LOCATIONID = ID from SPONSORSHIPLOCATION where HIERARCHYPATH = @HIERARCHYID
        set @DESIGNATIONID = dbo.UFN_SPONSORSHIP_GETDESIGNATIONBYLOCATION(@LOCATIONID)
      end
    else
      set @DESIGNATIONID = null

  return @DESIGNATIONID
end