UFN_EVENTSPONSOR_GETSPONSORCONSTITUENTID_BYCONSTITUENTID

CREATE function dbo.UFN_EVENTSPONSOR_GETSPONSORCONSTITUENTID_BYCONSTITUENTID
(
  @CONSTITUENTID uniqueidentifier,
  @EVENTID uniqueidentifier = null
)
returns uniqueidentifier
with execute as caller
as begin
    -- do work here and return a value

    declare @SPONSORCONSTITUENTID uniqueidentifier = null;

  select top 1 @SPONSORCONSTITUENTID = ES.CONSTITUENTID
  from EVENTSPONSOR ES(nolock)
  inner join CONSTITUENT C(nolock)
    on C.ID = ES.ORGANIZATIONCONSTITUENTID
    and C.ISORGANIZATION = 1
  where ES.ORGANIZATIONCONSTITUENTID = @CONSTITUENTID
    and ES.EVENTID = isnull(@EVENTID, ES.EVENTID);

  if @SPONSORCONSTITUENTID is null
    set @SPONSORCONSTITUENTID = @CONSTITUENTID;

  return @SPONSORCONSTITUENTID;
end