ID Number: Q75296
1.10
OS/2
buglist1.10
Problem ID: PRSQL9107020
SYMPTOM:
When using the IF EXISTS or IF NOT EXISTS in TRANSACT-SQL to check
for a table's existence, the user receives the following error:
There is already an object named '<table name>' in the database.
(Msg 2714, Level 16, State 1).
CAUSE:
SQL Server does not correctly interpret the IF [NOT] EXISTS in
TRANSACT-SQL when a CREATE TABLE statement is issued as a condition
of the IF [NOT] EXISTS statement. The following code segment
demonstrates a typical query that will produce the error. This
query fails only if the "authors" table exists when the query is
run; if it does not exist, the query runs as expected, and the new
table is created.
if not exists (select *
from sysobjects
where name = 'authors')
begin
print "Table does not exist...creating new table."
create table authors (col1 int, col2 int)
print "New table created."
end
else
print "Table already exists...cannot create it."
Notes
-----
1. If the CREATE TABLE... statement is removed, the query runs
properly (that is, the print statements are executed).
2. If the query is changed from IF NOT EXISTS to IF EXISTS, and the
logic is reversed, the statement will still fail.
RESOLUTION/STATUS:
Microsoft has confirmed this to be a problem in SQL Server version
1.1. We are researching this problem and will post new information
here as it becomes available.
One possible workaround is to put the IF [NOT] EXISTS logic into a
stored procedure. This will work provided that the table that is
being created does not exist at the time when the stored procedure
is created. However, once the procedure is created, it will execute
properly even when the table already exists. The following example
demonstrates this:
create procedure mk_table
as
if not exists (select *
from sysobjects
where name = 'authors')
begin
print "Table does not exist...creating new table."
create table authors (col1 int, col2 int)
print "New table created."
end
else
print "Table already exists...cannot create it."
If the authors table does not exist when this query is run, the
stored procedure will be properly created. Executing the stored
procedure will create the authors table and produce the following
output:
Table does not exist...creating new table.
New table created.
If the procedure is immediately executed again, the following
output will be displayed:
Table already exists...cannot create it.