UFN_INTERACTION_GETSINGLELINELOCATION

CREATE function dbo.UFN_INTERACTION_GETSINGLELINELOCATION
(
  @LOCATIONID uniqueidentifier,
  @OTHERLOCATION nvarchar(300)
)
returns nvarchar(300)
with execute as caller
as begin
  -- Neither an existing location nor a user-specified location was given

  if @LOCATIONID is null
  begin
    return '';
  end

  declare @LOCATION nvarchar(300) = '';

  -- Get location string

  if @LOCATIONID = '84A394FE-55B8-4737-9B2F-CC478766EC39' -- Well-known GUID for 'other' location

  begin
    -- Use specified other location

    set @LOCATION = ltrim(rtrim(@OTHERLOCATION));
  end
  else
  begin
    -- Get address from database

    select @LOCATION = dbo.UFN_BUILDFULLADDRESS(ID, ADDRESSBLOCK, CITY, STATEID, POSTCODE, COUNTRYID)
    from dbo.ADDRESS
    where ID = @LOCATIONID;

    -- Replace line breaks with spaces

    set @LOCATION = replace(@LOCATION, NCHAR(10), ' ');
    set @LOCATION = replace(@LOCATION, NCHAR(13), ' ');
  end

  -- Trim extra spaces by continuing to replace double spaces with single ones until only single spaces remain

  while @LOCATION <> replace(@LOCATION, '  ', ' ')
  begin
    set @LOCATION = replace(@LOCATION, '  ', ' ');
  end

  return @LOCATION;
end