UFN_VENDOR_FORMATADDRESS

create function dbo.UFN_VENDOR_FORMATADDRESS(
  @ADDRESSID uniqueidentifier
)
returns nvarchar(400)
with execute as caller
as begin
  declare @ADDRESS nvarchar(400);


    select
    @ADDRESS = (
    case 
      when ISPRIMARY = 1 then '(Primary) - ' 
      else '' 
    end
      + coalesce(dbo.UFN_ADDRESSTYPECODE_GETDESCRIPTION(ADDRESSTYPECODEID),'')
        + case 
        when ADDRESSTYPECODEID is not null then ' - ' 
        else '' 
      end
        + case
              when len(ADDRESSBlOCK) > 0 then substring(ADDRESSBLOCK, 0, case when charindex(char(10),ADDRESSBLOCK) = 0 then len(addressblock) + 1 else charindex(char(10),ADDRESSBLOCK) end)
                when len(CITY) > 0 then CITY
                when STATEID is not null then dbo.UFN_STATE_GETABBREVIATION(STATEID)
                when len(POSTCODE) > 0 then POSTCODE
            end)
        from dbo.ADDRESS A
        where A.ID = @ADDRESSID
            and (len(ADDRESSBLOCK) > 0 or len(CITY) > 0 or STATEID is not null or len(POSTCODE) > 0)
            and DONOTMAIL = 0;

    return @ADDRESS
end