====== pears.FormatSiteAddress ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."FormatSiteAddress" IS 
{create function FormatSiteAddress 

/* Application Maintained Function / Procedure - DO NOT EDIT*/

(in reqVacancyid char(20),in Capitalise smallint,in Style char(1))
returns char(300)
begin
  declare FormattedAddress char(380);
  declare addr1 char(40);
  declare addr2 char(40);
  declare addr3 char(40);
  declare town char(30);
  declare county char(30);
  declare country char(30);
  declare postcode char(30);
  declare Separator char(2);
  declare PrePostCodeSeparator char(2);
  /* 
  Set the seperator for block or line 
  */
  if style = 'B' then
    set Separator="char"(13)+"char"(10);
    set PrePostCodeSeparator=Separator
  else set Separator=', ';
    set PrePostCodeSeparator=' '
  end if;
  /*
  Fetch address fields
  */
  select vacancy.addr1,vacancy.addr2,vacancy.addr3,vacancy.town,vacancy.county,vacancy.country,vacancy.postcode into addr1,
    addr2,addr3,town,county,country,postcode from vacancy where
    vacancyid = reqvacancyid;
  /*
  If capitalise option is set, capitalise town. 
  */
  if Capitalise = 1 then set town=upper(Town)
  end if;
  /*
  Build Formatted Address
  */
  if isnull(addr1,'') <> '' then set FormattedAddress=FormattedAddress || trim(addr1)
  end if;
  if isnull(FormattedAddress,'') <> '' and isnull(addr2,'') <> '' then set FormattedAddress=FormattedAddress || Separator
  end if;
  if isnull(addr2,'') <> '' then set FormattedAddress=FormattedAddress || trim(addr2)
  end if;
  if isnull(FormattedAddress,'') <> '' and isnull(addr3,'') <> '' then set FormattedAddress=FormattedAddress || Separator
  end if;
  if isnull(addr3,'') <> '' then set FormattedAddress=FormattedAddress || trim(addr3)
  end if;
  if isnull(FormattedAddress,'') <> '' and isnull(town,'') <> '' then set FormattedAddress=FormattedAddress || Separator
  end if;
  if isnull(town,'') <> '' then set FormattedAddress=FormattedAddress || trim(town)
  end if;
  if isnull(FormattedAddress,'') <> '' and isnull(county,'') <> '' then set FormattedAddress=FormattedAddress || Separator
  end if;
  if isnull(county,'') <> '' then set FormattedAddress=FormattedAddress || trim(county)
  end if;
  if isnull(FormattedAddress,'') <> '' and isnull(country,'') <> '' then set FormattedAddress=FormattedAddress || Separator
  end if;
  if isnull(country,'') <> '' then set FormattedAddress=FormattedAddress || trim(country)
  end if;
  if isnull(FormattedAddress,'') <> '' and isnull(postcode,'') <> '' then set FormattedAddress=FormattedAddress || PrePostCodeSeparator
  end if;
  if isnull(postcode,'') <> '' then set FormattedAddress=FormattedAddress || trim(postcode)
  end if;
  return FormattedAddress
end
}
</code>