create procedure [dbo].[MssWebOverrideAccountProfileService]
    @orderId int,
    @accountProfileServiceId int,
    @accountProfileStatusId    int,
    @comment varchar( 256 ),
    @sysUserId int

as
set nocount on

;with MergeData as (
    
    select
        OrderId = @orderId,
        AccountProfileServiceId = @accountProfileServiceId
    from Orders
    inner join AccountProfileServices on
        Orders.AccountProfileFID = AccountProfileServices.AccountProfileFID and
        AccountProfileServices.AccountProfileServiceId = @accountProfileServiceId
    where Orders.PriKey = @OrderId
)
merge AccountProfileServicesOverride with (tablock) using MergeData on (
    AccountProfileServicesOverride.AccountProfileServiceFID = MergeData.AccountProfileServiceId and
    AccountProfileServicesOverride.OrderFID = MergeData.OrderId
)
when matched then update set
    AccountProfileServiceStatusFID = @accountProfileStatusId,
    Comment = @comment,
    ModifiedBySysUserFID = @sysUserId,
    ModifiedDate = getdate()
when not matched then
    insert (
        AccountProfileServiceFID,
        OrderFID,
        AccountProfileServiceStatusFID,
        Comment,
        ModifiedBySysUserFID,
        ModifiedDate)
    values(
        @accountProfileServiceId,
        @orderId,
        @accountProfileStatusId,
        @comment,
        @sysUserId,
        getdate()
)
;
GO
GRANT EXECUTE ON  [dbo].[MssWebOverrideAccountProfileService] TO [MssExec]
GO