PRB: CREATE TABLE Appears to Ignore IF Statements

ID: Q42417

The information in this article applies to:

Microsoft SQL Server versions 4.2x, 6.0, 6.5

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.

WORKAROUND

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 query words: Transact-SQL Windows NT

Keywords : kbprg SSrvGen SSrvServer SSrvWinNT
Version : winnt:4.2x,6.0,6.5
Platform : winnt
Issue type :