51

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.

sql sql-server sql-server-2005 tsql
share | improve this question | |
62
0

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

SET TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN 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 @idCOMMIT TRANSACTION

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

DECLARE @id AS INTSELECT @id = tableID FROM table (NOLOCK) WHERE fieldValue=@newValueIF @id IS NULLBEGIN    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 TRANSACTIONENDSELECT @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.

share | improve this answer | |
  • 6
    +1 I don't understand why the only answer that considers race conditions and concurrency is languishing on zero votes. – Martin Smith Sep 30 '12 at 16:07
  • 2
    May you expand to explain why ISOLATION LEVEL SERIALIZABLE is is necessary and what can happen if you don’t set that? – binki Sep 9 '13 at 17:18
  • 5
    @binki, when 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. – zvolkov Feb 24 '14 at 22:19
35
0
IF EXISTS (SELECT 1 FROM Table WHERE FieldValue='') BEGIN   SELECT TableID FROM Table WHERE FieldValue=''ENDELSEBEGIN   INSERT INTO TABLE(FieldValue) VALUES('')   SELECT SCOPE_IDENTITY() AS TableIDEND

See here for more information on IF ELSE

Note: written without a SQL Server install handy to double check this but I think it is correct

Also, I've changed the EXISTS bit to do SELECT 1 rather than SELECT * as you don't care what is returned within an EXISTS, as long as something isI've also changed the SCOPE_IDENTITY() bit to return just the identity assuming that TableID is the identity column

share | improve this answer | |
  • 2
    The 'SELECT 1' doesn't matter. Are you changing it just to point out that you don't care about the details? It doesn't help performance. – Rob Garrison Sep 28 '09 at 17:51
  • 4
    I prefer to avoid SELECT * amongst my code - it doesn't feel like a good habit to get into, so I usually do a SELECT 1 when doing an exists – Jane Sep 29 '09 at 20:13
  • The solution by @zvolkov is slightly better than this as it uses a variable and only has to SELECT FROM Table once rather than twice. – Neo Nov 13 '13 at 12:39
7
0

You were close:

IF EXISTS (SELECT * FROM Table WHERE FieldValue='')   SELECT TableID FROM Table WHERE FieldValue=''ELSEBEGIN   INSERT INTO TABLE (FieldValue) VALUES ('')   SELECT TableID FROM Table WHERE TableID=SCOPE_IDENTITY()END
share | improve this answer | |
2
0

You just have to change the structure of the if...else..endif somewhat:

if exists(select * from Table where FieldValue='') then begin  select TableID from Table where FieldValue=''end else begin  insert into Table (FieldValue) values ('')  select TableID from Table where TableID = scope_identity()end

You could also do:

if not exists(select * from Table where FieldValue='') then begin  insert into Table (FieldValue) values ('')endselect TableID from Table where FieldValue=''

Or:

if exists(select * from Table where FieldValue='') then begin  select TableID from Table where FieldValue=''end else begin  insert into Table (FieldValue) values ('')  select scope_identity() as TableIDend
share | improve this answer | |
2
0

It sounds like your table has no key. You should be able to simply try the INSERT: if it’s a duplicate then the key constraint will bite and the INSERT will fail. No worries: you just need to ensure the application doesn't see/ignores the error. When you say 'primary key' you presumably mean IDENTITY value. That's all very well but you also need a key constraint (e.g. UNIQUE) on your natural key.

Also, I wonder whether your procedure is doing too much. Consider having separate procedures for 'create' and 'read' actions respectively.

share | improve this answer | |
1
0
DECLARE @t1 TABLE (    TableID     int         IDENTITY,    FieldValue  varchar(20))--<< No empty stringIF EXISTS (    SELECT *    FROM @t1    WHERE FieldValue = '') BEGIN    SELECT TableID    FROM @t1    WHERE FieldValue=''ENDELSE BEGIN    INSERT INTO @t1 (FieldValue) VALUES ('')    SELECT SCOPE_IDENTITY() AS TableIDEND--<< A record with an empty string already existsIF EXISTS (    SELECT *    FROM @t1    WHERE FieldValue = '') BEGIN    SELECT TableID    FROM @t1    WHERE FieldValue=''ENDELSE BEGIN    INSERT INTO @t1 (FieldValue) VALUES ('')    SELECT SCOPE_IDENTITY() AS TableIDEND
share | improve this answer | |
1
0
create schema tableName authorization dbogoIF OBJECT_ID ('tableName.put_fieldValue', 'P' ) IS NOT NULL drop proc tableName.put_fieldValuegocreate proc tableName.put_fieldValue(@fieldValue int) asdeclare @tableid int = 0select @tableid = tableid from table where fieldValue=''if @tableid = 0 begin   insert into table(fieldValue) values('')   select @tableid = scope_identity()endreturn @tableidgodeclare @tablid int = 0exec @tableid = tableName.put_fieldValue('')
share | improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged sql sql-server sql-server-2005 tsql or ask your own question.