Avoiding IF.. ELSE by using WHERE EXISTS
-
Posted on January 29, 2012 by Derek Dieter
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 […]
Continue reading ...
