UFN_TRANSLATIONFUNCTION_VENDORADDRESS

create function dbo.UFN_TRANSLATIONFUNCTION_VENDORADDRESS
(
  @ID uniqueidentifier
) 
returns nvarchar(150) 
as
begin
    declare @s nvarchar(150);

    select 
    @s = case len(coalesce(ADDRESSTYPECODE.DESCRIPTION, ADDRESS.ADDRESSBLOCK))
            when 0 then 'Incomplete Address ' + CAST(ADDRESS.SEQUENCE AS nvarchar)
            else coalesce(ADDRESSTYPECODE.DESCRIPTION, ADDRESS.ADDRESSBLOCK)
        end
  from dbo.ADDRESS
    left join
          dbo.ADDRESSTYPECODE on ADDRESS.ADDRESSTYPECODEID = ADDRESSTYPECODE.ID
  where ADDRESS.ID = @ID;

    return @s;
end