UFN_BBNC_GETREFERENCENUMBERBYDESIGNATIONID

CREATE function dbo.UFN_BBNC_GETREFERENCENUMBERBYDESIGNATIONID
(
  @DESIGNATIONID uniqueidentifier
)
returns uniqueidentifier
with execute as caller
as begin

declare @REFERENCEID uniqueidentifier
select 
    top(1) @REFERENCEID = CHARITYCLAIMREFERENCENUMBER.ID 
  from 
    DESIGNATION
    left join CHARITYCLAIMREFERENCENUMBERSITE on CHARITYCLAIMREFERENCENUMBERSITE.SITEID  = dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(@DESIGNATIONID)
    left join CHARITYCLAIMREFERENCENUMBER on CHARITYCLAIMREFERENCENUMBERSITE.CHARITYCLAIMREFERENCENUMBERID = CHARITYCLAIMREFERENCENUMBER.ID
  where 
    DESIGNATION.ID = @DESIGNATIONID

if @REFERENCEID is null
  begin
    select
      top(1) @referenceid = CHARITYCLAIMREFERENCENUMBER.ID
    from 
          CHARITYCLAIMREFERENCENUMBER 
          left join CHARITYCLAIMREFERENCENUMBERSITE on CHARITYCLAIMREFERENCENUMBERSITE.CHARITYCLAIMREFERENCENUMBERID = CHARITYCLAIMREFERENCENUMBER.ID 
    where
      SITEID is null
  end

return @REFERENCEID

end