UFN_SPONSORSHIPLOCATION_AVAILABLEOPPORTUNITIES

create function dbo.UFN_SPONSORSHIPLOCATION_AVAILABLEOPPORTUNITIES(
    @ID uniqueidentifier
)
returns int
with execute as caller
as begin
    declare @RESULT int

    select @RESULT = count(*)
    from dbo.SPONSORSHIPLOCATION TOPLOCATION
    inner join dbo.SPONSORSHIPLOCATION LOCATIONS on LOCATIONS.HIERARCHYPATH.IsDescendantOf(TOPLOCATION.HIERARCHYPATH) = 1
    inner join dbo.SPONSORSHIPOPPORTUNITY on SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID = LOCATIONS.ID
    where TOPLOCATION.ID = @ID
    and SPONSORSHIPOPPORTUNITY.AVAILABILITYCODE = 0;

    return @RESULT
end