Skip to Content
0
Former Member
Dec 26, 2015 at 06:46 PM

Create Table Statement Error With If Exists Clause

440 Views

I am having an issue when attempting to deploy DDL statements to create tables more than once. The code below is supposed to only create the table if it does not exists but its only successful if it is wrapped in exec immediate

Please see example below - wondering if anyone experience the same issue

Using EXEC

-----------------

cat cr_tb_with_exec.sql

use Research

go

IF OBJECT_ID ('TestExec') IS NULL BEGIN exec('

create table TestExec (

UserID int not null ,

FirstName varchar(30) not null ,

LastName varchar(30) not null ,

CONSTRAINT PK_TestExec_UserID PRIMARY KEY CLUSTERED ( UserID )

)

lock datapages

')

END

Go

isql -Usa -S DEV -i cr_tb_with_exec.sql -o cr_tb_with_exec.sql.log

execute multiple times without error

Without EXEC

----------------------

cat cr_tb_no_exec.sql

use Research

go

IF OBJECT_ID ('TestExec') IS NULL BEGIN

create table TestExec (

UserID int not null ,

FirstName varchar(30) not null ,

LastName varchar(30) not null ,

CONSTRAINT PK_TestExec_UserID PRIMARY KEY CLUSTERED ( UserID )

)

lock datapages

END

Go

isql -Usa -SDEV -i cr_tb_no_exec.sql -o cr_tb_no_exec.sql.log

first time no error

re-executing causes an error - complain table already exists

cat cr_tb_no_exec.sql.log

Msg 2714, Level 16, State 1:

Server 'DEV', Line 2:

There is already an object named 'TestExec' in the database.

I have tested adding and dropping indexes, forein keys using the same logic and they worked fine

The issues seems to be only related to creating tables

For this reason the developers have opted to put all DDL statemenets in EXEC ('').

It is important for the code to be re-executables as the developers provide a new sql build each

which we deploy against existing database server.