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

11 comments
Tim 16 Sep 2016 at 5:13 pm

Unfortunately this doesn’t work to insert the the first record in the table.

Sudeep Saxena 11 Apr 2016 at 6:37 am

Bro in the first example you are missing the BEGIN after the first end. Please check!

Johnny Boy 15 Oct 2015 at 5:55 am

This here is a great article on the *fastest* way to insert a record where one doesn’t already exist:

cc.davelozinski.com/…/fastest-way-to-insert-new-records-where-one-doesnt-already-exist

for those speed freaks amongst us. 🙂

Rose 29 Aug 2015 at 6:58 am

Proczitav recept, xoetszca ispecz pirog. Tolko ytocznite pozalyjsta dozy myki! Tak , kak v naczale napisano 250 gramm, a v konce izgotovlenija napisano 2 st( emkost 250gr). Tak skolko ze myki nado? Spasibo.

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

Oleg Litvinov 04 Jun 2016 at 1:08 pm

I agree with Martin Smith. I still allows duplicates(I tested), unfortunatly 🙂

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

  • Thanks for visiting!
css.php