PRB: CREATE TABLE Appears to Ignore IF Statements

ID Number: Q42417

1.00 1.10 1.11 4.20

OS/2

Summary:

SYMPTOMS

If a CREATE TABLE statement is placed inside an IF block to

prevent re-creating a table that already exists, the duplicate

table name error will still be raised.

RESOLUTION

Apparently, a CREATE TABLE statement is validated before any of

the flow-of-control or PRINT statements are executed.

The following code works properly:

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME="PROTOCOL"

DROP TABLE PROTOCOL

GO

CREATE TABLE PROTOCOL (C1 int)

GO

The following code does not work properly:

IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME="PROTOCOL")

CREATE TABLE PROTOCOL (C1 int)

GO

Additional reference words: Transact-SQL