create procedure [dbo].[MssWebInsertUpdateOrderContactPhone]
    @inContactId int,
    @inPhoneType varchar(50),
    @inCountryId int,
    @inAreaCode PhoneAreaCode,
    @inLocalNumber PhoneLocalNumber,
    @inExtension PhoneExtension

as
begin
    set nocount on

    ;with PhoneData as
    (
        select
        ContactId = @inContactId,
        PhoneTypeId = OrderContactPhoneType.OrderContactPhoneTypeID
        from OrderContactPhoneType
        where OrderContactPhoneType.TypeName = @inPhoneType
    )
    merge OrderContactPhoneNumber using PhoneData on
        PhoneData.ContactId = OrderContactPhoneNumber.OrderContactFID and
        PhoneData.PhoneTypeId = OrderContactPhoneNumber.OrderContactPhoneTypeFId
    when not matched then
        insert(
            CountryCodeStandardFID,
            AreaCode,
            LocalNumber,
            Extension,
            OrderContactFID,
            OrderContactPhoneTypeFId
        )
        values(
            @inCountryId,
            @inAreaCode,
            @inLocalNumber,
            @inExtension,
            PhoneData.ContactId,
            PhoneData.PhoneTypeId
        )
    when matched then update set
        CountryCodeStandardFID = @inCountryId,
        AreaCode = @inAreaCode,
        LocalNumber = @inLocalNumber,
        Extension = @inExtension;
end
GO
GRANT EXECUTE ON  [dbo].[MssWebInsertUpdateOrderContactPhone] TO [MssExec]
GO