Microsoft KB Archive/42417

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 16:56, 18 July 2020 by 3155ffGd (talk | contribs) (importing KB archive)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Knowledge Base


PRB: CREATE TABLE Appears to Ignore IF Statements

Article ID: 42417

Article Last Modified on 3/14/2005



APPLIES TO

  • Microsoft SQL Server 4.21a Standard Edition
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition



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
                


Additional query words: Transact-SQL Windows NT

Keywords: kbprogramming KB42417