IF EXISTS, THEN SELECT ELSE INSERT AND THEN SELECT

Question

How do you say the following in Microsoft SQL Server 2005:

IF EXISTS (SELECT * FROM Table WHERE FieldValue='') THEN    SELECT TableID FROM Table WHERE FieldValue='' ELSE    INSERT INTO TABLE(FieldValue) VALUES('')    SELECT TableID FROM Table WHERE TableID=SCOPE_IDENTITY() END IF 

What I'm trying to do is to see if there is a blank fieldvalue already, and if there is then return that TableID, else insert a blank fieldvalue and return the corresponding primary key.

Answer 1

You need to do this in transaction to ensure two simultaneous clients won't insert same fieldValue twice:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION     DECLARE @id AS INT     SELECT @id = tableId FROM table WHERE fieldValue=@newValue     IF @id IS NULL     BEGIN        INSERT INTO table (fieldValue) VALUES (@newValue)        SELECT @id = SCOPE_IDENTITY()     END     SELECT @id COMMIT TRANSACTION 

you can also use Double-checked locking to reduce locking overhead

DECLARE @id AS INT SELECT @id = tableID FROM table (NOLOCK) WHERE fieldValue=@newValue IF @id IS NULL BEGIN     SET TRANSACTION ISOLATION LEVEL SERIALIZABLE     BEGIN TRANSACTION         SELECT @id = tableID FROM table WHERE fieldValue=@newValue         IF @id IS NULL         BEGIN            INSERT INTO table (fieldValue) VALUES (@newValue)            SELECT @id = SCOPE_IDENTITY()         END     COMMIT TRANSACTION END SELECT @id 

As for why ISOLATION LEVEL SERIALIZABLE is necessary, when you are inside a serializable transaction, the first SELECT that hits the table creates a range lock covering the place where the record should be, so nobody else can insert the same record until this transaction ends.

Without ISOLATION LEVEL SERIALIZABLE, the default isolation level (READ COMMITTED) would not lock the table at read time, so between SELECT and UPDATE, somebody would still be able to insert. Transactions with READ COMMITTED isolation level do not cause SELECT to lock. Transactions with REPEATABLE READS lock the record (if found) but not the gap.