Avoiding IF.. ELSE by using WHERE EXISTS
-
Posted on January 29, 2012 by Derek Dieter
-
0
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:
@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.
@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
- Comments (RSS)
- Trackback
- Permalink
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
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.
Pretty cool. Thank you!
