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

User · Answer

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

User · Answer

create schema tableName authorization dbo go IF OBJECT ID   tableName put fieldValue    P    IS NOT NULL  drop proc tableName put fieldValue go create proc tableName put fieldValue  fieldValue int  as declare  tableid int   0 select  tableid   tableid from table where fieldValue    if  tableid   0 begin    insert into table fieldValue  values        select  tableid   scope identity   end return  tableid go declare  tablid int   0 exec  tableid   tableName put fieldValue

User · Answer

DECLARE  t1 TABLE       TableID     int         IDENTITY      FieldValue  varchar 20     -- lt  lt  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  -- lt  lt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] IF EXISTS, THEN SELECT ELSE INSERT AND THEN SELECT

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql