-- получаем полное строковое представление адреса по коду
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetBaseAddressFullView]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetBaseAddressFullView]
GO
create function GetBaseAddressFullView(@AddressID integer)
returns nvarchar(4000)
as
begin
  declare @res nvarchar(4000)
  set @res = ''
  declare @FStreetID int
  declare @FLocality nvarchar(50)
  declare @FStreet nvarchar(50)
  declare @FEstate nvarchar(50)
  declare @FEstateType nvarchar(50)
  declare @FFraction nvarchar(50)
  declare @FHousing nvarchar(50)
  declare @FBuilding nvarchar(50)
  declare @FNote nvarchar(100) 
  select 
    @FStreetID = BP.StreetID,
    @FLocality = LT.Abbr + '. ' + L.Name,
    @FStreet = ST.Abbr + '. ' + S.Name,
    @FEstate = BP.Estate,
    @FEstateType = case BP.EstateType 
      when 0 then 'д. '
      when 1 then 'вл. '
      when 2 then 'уч. '
    end,
    @FFraction = BP.Fraction,
    @FHousing = BP.Housing,
    @FBuilding = BP.Building,
    @FNote = BP.Note 
    from AddressBaseParts BP
    inner join Localities L 
      inner join LocalityTypes LT on LT.TypeID = L.TypeID
      on L.LocalityID = BP.LocalityID    
    left join Streets S 
      inner join StreetTypes ST on ST.TypeID = S.TypeID
      on S.StreetID = BP.StreetID
    where BP.AddressID = @AddressID

  if @FEstate <> ''
  begin
    set @res = @FEstateType + @FEstate
    if @FFraction <> ''
      set @res = @res + '/' + @FFraction
  end
  if @FHousing <> ''
    set @res = @res + ' корпус ' +  @FHousing
  if @FBuilding <> ''
    set @res = @res + ' строение ' +  @FBuilding

  if (IsNull(@FStreetID,0) > 0)
    if @res <> ''
      set @res = @FStreet + ', ' + @res
    else
      set @res = @FStreet

  if @FNote <> '' 
    set @res = @res + ' (' +  @FNote + ')'

  return @res
end