IF NOT EXISTS syntax

Question

Post reply

IF NOT EXISTS syntax

smknox

Hall of Fame

Points: 3275
More actions
March 12, 2010 at 3:43 pm

#216660

I use "IF NOT EXISTS" to check for the existence of a record. If none exists, I want to insert records into 3 tables. How do I indicate the end of the routine? In other words: IF NOT EXISTS(SELECT ID FROM Table1 WHERE

ID=2)

Insert Into Table2 ...

Insert Into Table3 ...

Insert Into Table4 ...

From here I want to continue with updates to tables based on a passed in ID. But what syntax do I need to indicate this is the end of the "IF NOT EXISTS" clause and to continue with updates?

Thanks,

Sam

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 1

Elliott Whitlow

SSC Guru

Points: 102296

March 12, 2010 at 3:47 pm

#1133050

IF NOT EXISTS(SELECT ID FROM Table1 WHERE ID=2)

BEGIN

Insert Into Table2 ...

Insert Into Table3 ...

Insert Into Table4 ...

END

CEWII

Kingston Dhasian SSCoach Points: 19795 More actions · Answer 2

smknox (3/12/2010)

IF NOT EXISTS(SELECT ID FROM Table1 WHERE ID=2)

Insert Into Table2 ...

Insert Into Table3 ...

Insert Into Table4 ...

From here I want to continue with updates to tables based on a passed in ID. But what syntax do I need to indicate this is the end of the "IF NOT EXISTS" clause and to continue with updates?

If you are thinking that all three Inserts would happen based on your clause you are wrong. Your clause has ended after the first Insert itself. You have to use a BEGIN...END to ensure all three Inserts happen based on your IF Clause.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Paul White SSC Guru Points: 150457 More actions · Answer 3



BEGIN TRY

    BEGIN TRANSACTION;

    SAVE TRANSACTION [DoInserts];

    IF  NOT EXISTS

        (

        SELECT  *

        FROM    dbo.Table1 WITH (UPDLOCK, HOLDLOCK)

        WHERE   ID = 2

        )

    BEGIN

        INSERT  dbo.Table1 ...

        INSERT  dbo.Table2 ...

        INSERT  dbo.Table3 ...

        INSERT  dbo.Table4 ...

    END;

    COMMIT TRANSACTION;

END TRY;

BEGIN CATCH

    IF  XACT_STATE() = -1 ROLLBACK TRANSACTION;

    IF  XACT_STATE() = 1

    BEGIN

        ROLLBACK TRANSACTION [DoInserts];

        COMMIT TRANSACTION;

    END;

    -- Rethrow error or other handling here

END CATCH;