create procedure [dbo].[MssWebGetBuildingAddress]
    @inBuildingId int
as
begin
    ;with MainAddressType as(
        select AddressTypeID from AddressType where TypeName = 'Main'
    )
    select
        Id = Buildings.BuildingID,
        CrossStreet = Buildings.CrossStreet,
        ServiceEntrance = Buildings.ServiceEntrance,
        SiteNote = Buildings.SiteNote,
        AddressLocationTypeId = Buildings.AddressLocationTypeFID,
        AddressTypeId = isnull( BuildingAddress.AddressTypeFID, MainAddressType.AddressTypeID),
        AddessId = BuildingAddress.BuildingAddressID,
        Address1 = BuildingAddress.Address1,
        Address2 = BuildingAddress.Address2,
        Address3 = BuildingAddress.Address3,
        City = BuildingAddress.City,
        [State] = BuildingAddress.State,
        PostalCode = BuildingAddress.PostalCode,
        CountryName = isnull(AddressCountryCodeStandard.CountryName,DefaultCountry.CountryName),
        CountryCodeStandardId = isnull(BuildingAddress.CountryCodeStandardFID,DefaultCountry.CountryCodeStandardId)
    from Buildings
    cross join dbo.GetDefaultCountry() DefaultCountry
    cross join MainAddressType
    left outer join BuildingAddress on Buildings.BuildingID = BuildingAddress.BuildingFID
    left outer join CountryCodeStandard AddressCountryCodeStandard on BuildingAddress.CountryCodeStandardFID = AddressCountryCodeStandard.CountryCodeStandardID
    where Buildings.BuildingID = @inBuildingId

end
GO
GRANT EXECUTE ON  [dbo].[MssWebGetBuildingAddress] TO [MssExec]
GO