Why would IF EXISTS not work?

Question

I have a lot of code I am trying to run where I'm querying the sysobjects table to check if an object exists before I drop it and create it again.

Issue being, sometimes if I go:

if not exists (select name from sysobjects o where o.name = 'my_table' and o.type =  'U') 
CREATE TABLE my_table (..)
go

it works, no worries. However, when I came back to run it again, I get this lovely error:

SQL Server Error on (myserver) Error:2714 at Line:10 Message:There is already an object named 'my_table' in the database.

Thanks for that, SQL Programmer. I actually asked for you not to create this table if it already exists. -_-

Any ideas?

Answer 1

The sybase parsers object validation pass is global and not based on conditional evaluation. Even though your code can not execute CREATE TABLE the statement is still checked for syntax and applicability which fails when the system sees that the table already exists.

The only way around this that I know of is to put your create statements inside of an EXEC() which would be evaluated only if the section was executed.

Answer 2

the logic to what you are doing doesn't seem quite right. based on your statement:

"I am trying to run where I'm querying the sysobjects table to check if an object exists before I drop it and create it again"

you should simply do a delete followed by a create. This way is usually better because it ensures that the table will be updated. if the table existed and you had changes, you are probably not getting what you want.

The immediate issue you are running into is an assumed db ownership that was not consistent between runs.

based on your clarification below - here is what you can do:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[XXXX]') AND type in (N'U')) DROP TABLE [dbo].[XXXX] GO

CREATE TABLE [dbo].[XXXX(... GO

you can run this over and over again...

Answer 3

yes, the entire batch of SQL is normalized and compiled so as to create an "execution plan" for the entire batch. During normalization, the "possible" "create table" statement is a problem if it already exists at compile time.

My solution: rename -

if exists (select 1 from ....) begin drop table xyz create table xyz_zzzz ( ... ) exec sp_rename 'xyz_zzzz','xyz' end