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 were close:

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

Answer 2

IF EXISTS (SELECT 1 FROM Table WHERE FieldValue='') 
BEGIN
   SELECT TableID FROM Table WHERE FieldValue=''
END
ELSE
BEGIN
   INSERT INTO TABLE(FieldValue) VALUES('')
   SELECT SCOPE_IDENTITY() AS TableID
END

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 is I've also changed the SCOPE_IDENTITY() bit to return just the identity assuming that TableID is the identity column

Answer 3

On the application side you could do it with one statement like this:

INSERT INTO TABLE(FieldValue) 
SELECT '' WHERE NOT EXISTS (SELECT TableID FROM TABLE WHERE FieldValue='');
SELECT TableID FROM TABLE WHERE FieldValue=''

Answer 4

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=''
    IF @id IS NULL
    BEGIN
       INSERT INTO TABLE (FieldValue) VALUES ('')
       SELECT @id = SCOPE_IDENTITY()
    END
    select @id
COMMIT TRANSACTION

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

declare @id as int
SELECT @id = TableID FROM Table (nolock) WHERE FieldValue=''
IF @id IS NULL
BEGIN
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    BEGIN TRANSACTION
        SELECT @id = TableID FROM Table WHERE FieldValue=''
        IF @id IS NULL
        BEGIN
           INSERT INTO TABLE (FieldValue) VALUES ('')
           SELECT @id = SCOPE_IDENTITY()
        END
    COMMIT TRANSACTION
END
select @id

Answer 5

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 ('')
end
select 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 TableID
end

Answer 6

DECLARE @t1 TABLE (
    TableID     int         IDENTITY,
    FieldValue  varchar(20)
)

--<< No empty string
IF EXISTS (
    SELECT *
    FROM @t1
    WHERE FieldValue = ''
) BEGIN
    SELECT TableID
    FROM @t1
    WHERE FieldValue=''
END
ELSE BEGIN
    INSERT INTO @t1 (FieldValue) VALUES ('')
    SELECT SCOPE_IDENTITY() AS TableID
END

--<< A record with an empty string already exists
IF EXISTS (
    SELECT *
    FROM @t1
    WHERE FieldValue = ''
) BEGIN
    SELECT TableID
    FROM @t1
    WHERE FieldValue=''
END
ELSE BEGIN
    INSERT INTO @t1 (FieldValue) VALUES ('')
    SELECT SCOPE_IDENTITY() AS TableID
END

Answer 7

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.