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:

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

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.

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

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

6 comments
Novice 06 Mar 2013 at 10:45 pm

If the record does already exist, I need my stored procedure to return the ID.

I’ve tried a couple of things but haven’t come up with a working syntax.

Is there a way to return the ID of the record if it does already exist?

Thanks!

Tiago Silva 30 Jan 2013 at 6:21 pm

I made a example if you want to see:

ALTER PROCEDURE [dbo].[USP_getCliente]

@id int

AS
BEGIN

SET NOCOUNT ON;

— programa começa aqui
–verifica se já existe tabela,
–se não, cria a tabela e insere o registo,
if not exists (SELECT * from sysobjects
where name = ‘aux_pt_clientes’ and xtype = ‘U’)
begin
create table aux_pt_clientes
(
id int IDENTITY(1,1),
bi int,
nome varchar(20),
morada varchar(50),
email varchar(50)
)
insert into aux_pt_clientes
(
bi,
nome,
morada,
email
)
select bi, nome, morada, email
from pt_clientes
where idCliente = @id
end

–verifica se já existe tabela,
–se sim, então insere ou faz update,

else if exists (SELECT * from sysobjects
where name = ‘aux_pt_clientes’ and xtype = ‘U’)
begin

if not exists (SELECT bi
from aux_pt_clientes
where id = @id)
begin
insert into aux_pt_clientes
(
bi,
nome,
morada,
email
)
select bi, nome, morada, email
from pt_clientes
where idCliente = @id

print ‘Adicionado com Sucesso’
end
else
begin
update aux_pt_clientes
set aux_pt_clientes.morada = ptc.morada,
aux_pt_clientes.email = ptc.email,
aux_pt_clientes.nome = ptc.nome
from pt_clientes ptc
where aux_pt_clientes.bi = ptc.bi

print ‘Update with Sucess’
end
end
END

Martin Smith 29 Dec 2012 at 11:57 am

This approach won’t guarantee no duplicates either unless additional locking hints are used. See http://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there

Azeheruddin khan 08 Feb 2012 at 3:24 am

Hi Derek Dieter,

Thanks for posting valuable articles. Currently i am using lots of if…..else condition in my stored procedure. After seeing this articles i am planning to change my if..else with IF NOT EXISTS, but i am getting a small problem while using this query.This query is not working for tables that do’t have any records.Can u help me regarding this.

Ronny Heuschkel 09 Aug 2013 at 4:38 pm

If you remove the FROM in the SELECT it also works on empty tables:

INSERT INTO
SELECT TOP 1 ,
WHERE NOT EXISTS(SELECT 1 FROM WHERE = )

Anonymous 29 Jan 2012 at 3:03 pm

Pretty cool. Thank you!

Featured Articles

 Site Author