PRB: CREATE TABLE Appears to Ignore IF Statements

Last reviewed: April 30, 1997
Article ID: Q120875

The information in this article applies to:

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server version 4.2

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

When a query is submitted, object existence and resolution is done at parse time, not execution time. This will cause the CREATE TABLE statement to fail the query with the 2714 error.

The following code works properly:

   IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME="TEST")
     DROP TABLE TEST
   GO

   CREATE TABLE TEST (C1 int)
   GO

The following code does not work properly:

   IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME="TEST")
  CREATE TABLE TEST (C1 int)
   GO

Since there are times when you do not want to drop the table, the following procedure can be used:

Run the following script once on each server where the table needs to used, preferably when nobody else is accessing the table. The script will create a stored procedure that will build the table. It is necessary to rename the table so that the stored procedure TAB_CREATE will compile without error when created.

   IF EXISTS(SELECT * FROM SYSOBJECTS WHERE id=OBJECT_ID("TEST"))
        EXEC SP_RENAME TEST,TEST1
   GO
   CREATE PROC TAB_CREATE
        AS CREATE TABLE TEST (C1 int)
   IF EXISTS(SELECT * FROM SYSOBJECTS WHERE id=OBJECT_ID("TEST1"))
        EXEC SP_RENAME TEST1,TEST

Once the above script has been run, the following code can be used in scripts to resolve the duplicate table name error:

   IF EXISTS(SELECT * FROM SYSOBJECTS WHERE id=OBJECT_ID("TEST"))
        PRINT 'The table already exists...'
   ELSE
   BEGIN
        PRINT 'Create the table...'
        EXEC TAB_CREATE
   END


Additional query words: Transact-SQL Windows NT
Keywords : kbprg SSrvServer SSrvWinNT
Version : 4.2 | 4.2
Platform : OS/2 WINDOWS


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 30, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.