create procedure [dbo].[MssWebGetOrderLocationBuildingConstraint]
    @inOrderId int,
    @inLocationId int
as
begin
    set nocount on
    
    ;with MainAddressType as(
        select AddressTypeId from AddressType where TypeName = 'Main'
    )
    select
        Id = OrderLocations.OrderLocationId,
        Name = OrderLocations.Name,
        CrossStreet = OrderLocations.CrossStreet,
        ServiceEntrance = OrderLocations.ServiceEntrance,
        SiteNote = OrderLocations.SiteNote,
        AddessId = OrderLocationAddress.OrderLocationAddressID,
        Address1 = OrderLocationAddress.Address1,
        Address2 = OrderLocationAddress.Address2,
        Address3 = OrderLocationAddress.Address3,
        City = OrderLocationAddress.City,
        State = OrderLocationAddress.State,
        PostalCode = OrderLocationAddress.PostalCode,
        CountryName = isnull(AddressCountryCodeStandard.CountryName,DefaultCountry.CountryName),
        CountryCodeStandardId = isnull(AddressCountryCodeStandard.CountryCodeStandardID,DefaultCountry.CountryCodeStandardId)
    from OrderLocations
    cross join dbo.GetDefaultCountry() DefaultCountry
    left outer join OrderLocationAddress on
        OrderLocationAddress.OrderLocationFID = OrderLocations.OrderLocationID
    left outer join MainAddressType on OrderLocationAddress.AddressTypeFID = MainAddressType.AddressTypeId
    left outer join CountryCodeStandard AddressCountryCodeStandard on OrderLocationAddress.CountryCodeStandardFID = AddressCountryCodeStandard.CountryCodeStandardID
    where OrderLocations.OrderFID = @inOrderId and OrderLocations.OrderLocationID = @inLocationId;
end
GO
GRANT EXECUTE ON  [dbo].[MssWebGetOrderLocationBuildingConstraint] TO [MssExec]
GO