Which is better approach?

Question

Which is better approach?

S_Kumar_S

SSCrazy Eights

Points: 9037
More actions
September 28, 2012 at 3:23 am

#268317

Hi Friends
I do a check on a table if a particular record exists in table using IF EXISTS(SELECt 1 FROM tab1 WHERE id=1) inside a proc. Now if record exists, i run an update statement else I run an INSERT statement.
Another way of doing it can be : create a unique index on id column. Directly run the INSERT proc from c# code. If record exists, error will be thrown.So control go to CATCH block and there I call the UPDATE proc.
In both approaches, 2 statements will be executed on database. In first case SELECT and INSERT/UPDATE. In second case , INSERT and UPDATE.
Which approach is better to follow AND WHY?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 1

Error trapping is for errors. It may appear similar at a code level but under the hood it uses many more recources to process (according to posts on SSC).

It's also confusing to people trying to maintain it.

I would only ever use error trapping to trap errors, & make my code as obvious & intuitive as possible!

You can consider using MERGE too...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

S_Kumar_S (9/28/2012)
Hi Friends
I do a check on a table if a particular record exists in table using IF EXISTS(SELECt 1 FROM tab1 WHERE id=1) inside a proc. Now if record exists, i run an update statement else I run an INSERT statement.
Another way of doing it can be : create a unique index on id column. Directly run the INSERT proc from c# code. If record exists, error will be thrown.So control go to CATCH block and there I call the UPDATE proc.
In both approaches, 2 statements will be executed on database. In first case SELECT and INSERT/UPDATE. In second case , INSERT and UPDATE.
Which approach is better to follow AND WHY?

Neither, use MERGE, that's what it's there for - single statement upserts.

A less drastic approach than "Another way" above would be to do an update and capture the number of rows updated - if none, then do the insert. Apart from that I agree with Laurie - use error handling for handling errors, not for dodgy logic.

If you have an id column on a table, shouldn't you already have a unique index on it? You can't really call it an id column if it allows duplicate values.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden