CODEWITHSUNDEEP
sql-serversql-server-2005t-sqlstored-procedures
Jason
Jason

Reputation: 17956

check for existence before insertion in stored procedure

In my stored procedure, I would like to check to make sure what I am trying to insert doesn't already exist in the table. I have tried the code below, but it seems to be giving me false positives (aka comes out to true even when it's not in the table). Is there a better way?

if not exists (select myID from tableName where myID = @myID and otherColumn = @otherColumn)
begin
    insert into tableName
        (
            myID
            , otherColumn
        ) values (
            @myID
            , @otherColumn
        )
end

Upvotes: 2

Views: 550

Answers (5)

Jason
Jason

Reputation: 17956

The problem was not with the if statement. Sadly it was with my logic that was sending values to the stored procedure.

Upvotes: 0

DJO
DJO

Reputation: 11

I take it there is a unique key over myID and otherColumn.

If so how likely is it to be breached by a race condition. Prehaps you just need do the insert and handle any exception or probabaly better yet just allow the exception to be raised to the caller (after all it may want to inform a user of this situation).

Upvotes: 0

Philip Kelley
Philip Kelley

Reputation: 40289

Two thoughts.

First, if there are NULLs involved, your "where exists" logic may not work as you'd like it.

Second, I'd try and make it one statement (because of the ACID properties of relational databases), rather than messing with transactions, locks, and their good friends blocking and deadlocking. The following statement works:

INSERT tableName (myId, otherColumn)
 select @myId, @otherColumn
 except select myId, otherColumn
  from tableName

This might not work for you, depending on table size and/or indexing issues; other variants are possible, depending on your circumstances.

Upvotes: 3

SQLMenace
SQLMenace

Reputation: 134923

add UPDLOCK and HOLDLOCK hints to your SELECT statement and use a transaction

Upvotes: 1

Arthur
Arthur

Reputation: 8129

Maybe a concurrency problem? If so, try to create a transaction and select data with UPDLOCK hint.

Upvotes: 1

Related Questions