check for existence before insertion in stored procedure

Question

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

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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).

Answer 5

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