UFN_SPONSORSHIPOPPORTUNITY_GETDESIGNATION

create function dbo.UFN_SPONSORSHIPOPPORTUNITY_GETDESIGNATION(
    @ID uniqueidentifier
)
returns uniqueidentifier
with execute as caller
as begin
    declare @DESIGNATIONID uniqueidentifier
    declare @LOCATIONID uniqueidentifier

    select @DESIGNATIONID = SPONSORSHIPOPPORTUNITYPROJECT.DESIGNATIONID,
           @LOCATIONID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID
    from dbo.SPONSORSHIPOPPORTUNITY
    left outer join dbo.SPONSORSHIPOPPORTUNITYPROJECT on SPONSORSHIPOPPORTUNITYPROJECT.ID = SPONSORSHIPOPPORTUNITY.ID
    where SPONSORSHIPOPPORTUNITY.ID = @ID

    if @DESIGNATIONID is null
        set @DESIGNATIONID = dbo.UFN_SPONSORSHIP_GETDESIGNATIONBYLOCATION(@LOCATIONID);

    return @DESIGNATIONID;
end