Skip to main content

Not only is this able to run concurrently, but it’s also cleaner and uses half the scans (meaning less disk io).

create procedure p_customer_totals_ins_upd
    @cust_id int,
    @order_amt numeric
    declare @rowcount int; -- store the number of rows that get inserted

    insert into customer_totals(
    select top 1 cust_id = @cust_id, -- important since we're not constraining any records
        order_amt = @order_amt
    from customer_totals as ct
    where not exists (select 1 from customer_totals where cust_id = @cust_id); -- this replaces the if statement

    set @rowcount = @@ROWCOUNT; -- return back the rows that got inserted
    update customer
    set order_amt = order_amt + @order_amt
    where @rowcount = 0 and cust_id = @cust_id; -- if no rows were inserted, the cust_id must exist, so update