UFN_SPONSORSHIPOPPORTUNITY_OVERRIDES_SPONSORSPEROPPORTUNITY

create function dbo.UFN_SPONSORSHIPOPPORTUNITY_OVERRIDES_SPONSORSPEROPPORTUNITY(
  @SPONSORSHIPLOCATIONID uniqueidentifier,
  @OVERRIDES xml
)
returns smallint
with execute as caller
as begin
    declare @SPONSORSPEROPPORTUNITY smallint;

  if @OVERRIDES is null
    return null

    begin
        select @SPONSORSPEROPPORTUNITY = OVERRIDES.SPONSORSPEROPPORTUNITY
        from dbo.UFN_SPONSORSHIPOPPORTUNITYGROUP_OVERRIDESPONSORSPEROPPORTUNITY(@OVERRIDES) OVERRIDES
        inner join dbo.SPONSORSHIPLOCATION OPPORTUNITYLOCATION on OPPORTUNITYLOCATION.ID = @SPONSORSHIPLOCATIONID
        inner loop join dbo.SPONSORSHIPLOCATION OVERRIDELOCATION on OVERRIDELOCATION.ID = OVERRIDES.SPONSORSHIPLOCATIONID
        where OPPORTUNITYLOCATION.HIERARCHYPATH.IsDescendantOf(OVERRIDELOCATION.HIERARCHYPATH) = 1;
    end

    return @SPONSORSPEROPPORTUNITY

end