Avoiding IF.. ELSE by using WHERE EXISTS

When coming from a programming background, it’s natural to want to frame conditional operations within the familiar if.. else constructs. This happens in a lot of SQL code I have worked with (and I used to be a contributor also).

The technique below is based on a common scenario. Say you want to insert new records that do not exist or update records that do exist. The familiar programming way of thinking typically leads to the following method:

[cc lang=”sql”]
CREATE PROCEDURE p_customer_totals_ins_upd
@cust_id int,
@order_amt numeric
AS

IF NOT EXISTS (SELECT 1 FROM customer_totals WHERE cust_id = @cust_id)
BEGIN
INSERT INTO customer_totals
(
cust_id,
order_amt
)
SELECT
cust_id = @cust_id
,order_amt = @order_amt
END
ELSE

UPDATE customer
SET order_amt = order_amt + @order_amt
WHERE cust_id = @cust_id

END
[/cc]

The problem with the above method is that it does not allow for concurrency. Meaning, if this procedure is called through two different connections at the exact same time, the chances are good the same cust_id will be inserted into the customer_totals table twice. This is because the IF NOT EXISTS statement operates in a separate transaction as the insert statement. So then the answer should be to wrap a BEGIN TRAN around the whole thing right??… No please don’t.. This will only increase the chance of deadlocks in your system.. and there’s a better way.

Using NOT EXISTS for Inserts

The answer comes by understanding that SQL statements by themselves are a transaction. So within the same transaction as the insert we can determine if the cust_id already exists.  I should mention there is one caveat for using this method.  At least one record needs to exist in customer_totals.

[cc lang=”sql”]

CREATE PROCEDURE p_customer_totals_ins_upd
@cust_id int,
@order_amt numeric
AS

DECLARE @rowcount int; — store the number of rows that get inserted

INSERT INTO customer_totals
(
cust_id,
order_amt
)
SELECT TOP 1 — important since we’re not constraining any records
cust_id = @cust_id
,order_amt = @order_amt
FROM customer_totals ct
WHERE NOT EXISTS — this replaces the if statement
(
SELECT 1
FROM customer_totals
WHERE cust_id = @cust_id
)

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

END

[/cc]

Not only is this able to run concurrently, but it’s also cleaner and uses half the scans (meaning less disk io). From here, you could make the argument that MERGE would be a better option. For now, I try to avoid merge for various reasons. You can find them here: The MERGE Statement