PRB: CREATE TABLE Appears to Ignore IF Statements (42417)

The information in this article applies to:

Microsoft SQL Server 4.2x
Microsoft SQL Server 6.0
Microsoft SQL Server 6.5

This article was previously published under Q42417

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

Modification Type:	Minor	Last Reviewed:	3/14/2005
Keywords:	kbProgramming KB42417