create procedure [dbo].[MssWebInsertUpdateOrderContactAddress]
    @inContactId int,
    @inAddressLine1 Address,
    @inAddressLine2 Address,
    @inAddressLine3 Address,
    @inCity AddressCity,
    @inState AddressState,
    @inCountryId int,
    @inPostalCode AddressPostalCode

as
begin
    set nocount on

    ;with MainAddressType as
    (
        select AddressType.AddressTypeID
        from AddressType where AddressType.TypeName = 'Main'
    )
    , InsertionData as
    (
        select
            ContactId = @inContactId,
            AddressTypeId = MainAddressType.AddressTypeID
        from MainAddressType
    )
    merge OrderContactAddress using InsertionData on
        OrderContactAddress.OrderContactFId = InsertionData.ContactId and
        OrderContactAddress.AddressTypeFID = InsertionData.AddressTypeId
    when not matched then insert
    (
        OrderContactFId,
        AddressTypeFID,
        Address1,
        Address2,
        Address3,
        City,
        State,
        PostalCode,
        CountryCodeStandardFID
    )
    values
    (
        @inContactId,
        AddressTypeId,
        @inAddressLine1,
        @inAddressLine2,
        @inAddressLine3,
        @inCity,
        @inState,
        @inPostalCode,
        @inCountryId
    )
    when matched then update set
        Address1 = @inAddressLine1,
        Address2 = @inAddressLine2,
        Address3 = @inAddressLine3,
        City = @inCity,
        State = @inState,
        PostalCode = @inPostalCode,
        CountryCodeStandardFID = @inCountryId;
end
GO
GRANT EXECUTE ON  [dbo].[MssWebInsertUpdateOrderContactAddress] TO [MssExec]
GO