create procedure [dbo].[MssWebInsertUpdateOrderLocationAddress]
    @inLocationId int,
    @inAddressLocationTypeId int,
    @inAddressLine1 Address,
    @inAddressLine2 Address,
    @inAddressLine3 Address,
    @inCity AddressCity,
    @inState AddressState,
    @inCountryId int,
    @inPostalCode AddressPostalCode
as
begin
    ;with MainAddressType as
        (
            select AddressType.AddressTypeID
            from AddressType where AddressType.TypeName = 'Main'
        ),
    InsertionData as
    (
        select AddressLocationTypeId = @inAddressLocationTypeId, AddressTypeId = MainAddressType.AddressTypeID
        from MainAddressType
            
    )
    merge OrderLocationAddress with (tablock) using InsertionData on
        OrderLocationAddress.OrderLocationFID =  @inLocationId
    when not matched then insert
    (
        OrderLocationFId,
        AddressLocationTypeFId,
        AddressTypeFId,
        Address1,
        Address2,
        Address3,
        City,
        State,
        PostalCode,
        CountryCodeStandardFID
    )
    values
    (
        @inLocationId,
        AddressLocationTypeId,
        AddressTypeId,
        @inAddressLine1,
        @inAddressLine2,
        @inAddressLine3,
        @inCity,
        @inState,
        @inPostalCode,
        @inCountryId
    )
    when matched then update set
        AddressLocationTypeFId = AddressLocationTypeId,
        Address1 = @inAddressLine1,
        Address2 = @inAddressLine2,
        Address3 = @inAddressLine3,
        City = @inCity,
        State = @inState,
        PostalCode = @inPostalCode,
        CountryCodeStandardFID = @inCountryId;
end
GO
GRANT EXECUTE ON  [dbo].[MssWebInsertUpdateOrderLocationAddress] TO [MssExec]
GO