|
Home > Archive > ASE Database forum > October 2005 > T-SQL IF issues
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| dontspam.hws@willspc.net 2005-10-28, 9:24 am |
| I'm having an issue with some t-sql...
I'm trying to check for the existence of a table before
creation...
IF (select count(*) from sysobjects where name = "mytable" )
= 0
create table dbo.mytable (
my_field1 char(6) not null,
my_field2 char(50) not null,
valid int DEFAULT 1
)
ELSE
IF (select count(*) from sysobjects, syscolumns where
sysobjects.name="mytable" and sysobjects.id = syscolumns.id
and syscolumns.name="valid") = 0
alter table mytable add valid int default 1
When I try executing it, I get the following error:
Server Message: Number 2714, Severity 16
Line 2:
There is already an object named mytable' in the database
remove the dontspam {period} for email
| |
|
| > I'm having an issue with some t-sql...
>
> I'm trying to check for the existence of a table before
> creation...
>
> IF (select count(*) from sysobjects where name = "mytable"
> ) = 0
> create table dbo.mytable (
> my_field1 char(6) not null,
> my_field2 char(50) not null,
> valid int DEFAULT 1
> )
> ELSE
> IF (select count(*) from sysobjects, syscolumns where
> sysobjects.name="mytable" and sysobjects.id =
> syscolumns.id and syscolumns.name="valid") = 0
> alter table mytable add valid int default 1
>
>
> When I try executing it, I get the following error:
>
> Server Message: Number 2714, Severity 16
> Line 2:
> There is already an object named mytable' in the database
>
> remove the dontspam {period} for email
You cannot have a create table SQL compile when the table
exists, even inside a conditional wrapper. I ususally put
the create statement inside a stored procedure, and call the
procedure whenever the table must be created.
If not exists (select name from sysobjects where name =
'Mytable')
begin
exec MyProcToCreateTable
end
| |
| dontspam.hws@willspc.net 2005-10-28, 9:24 am |
| > > I'm having an issue with some t-sql...
> database >
>
> You cannot have a create table SQL compile when the table
> exists, even inside a conditional wrapper. I ususally put
> the create statement inside a stored procedure, and call
> the procedure whenever the table must be created.
>
> If not exists (select name from sysobjects where name =
> 'Mytable')
> begin
> exec MyProcToCreateTable
> end
Thanks... that's a pain, but ... oh well. They should be
smarter to let me do this.
| |
| dontspam.hws@willspc.net 2005-10-28, 9:24 am |
| > > I'm having an issue with some t-sql...
> database >
>
> You cannot have a create table SQL compile when the table
> exists, even inside a conditional wrapper. I ususally put
> the create statement inside a stored procedure, and call
> the procedure whenever the table must be created.
>
> If not exists (select name from sysobjects where name =
> 'Mytable')
> begin
> exec MyProcToCreateTable
> end
I tried creating the procedure to do this, but I have the
same problem.
The tables already exist at most locations who will receive
this script. I want to write a universal upgrade script
that deals with sites no matter what level of the DB they
have.
I tried doing a create procedure createmytable as
BEGIN
create table dbo.mytable(
myfield char(6) not null,
valid int DEFAULT 1
)
END
And it still returned the same error.
Server Message: Number 2714, Severity 16
Procedure 'createmytable', Line 3:
There is already an object named 'mytable' in the database.
| |
| Bret Halford 2005-10-28, 9:24 am |
|
wk wrote:
>
> You cannot have a create table SQL compile when the table
> exists, even inside a conditional wrapper. I ususally put
> the create statement inside a stored procedure, and call the
> procedure whenever the table must be created.
>
> If not exists (select name from sysobjects where name =
> 'Mytable')
> begin
> exec MyProcToCreateTable
> end
I think it is slightly easier to use dynamic sql:
IF (select count(*) from sysobjects where name = "mytable" ) = 0
EXECUTE(" create table dbo.mytable (
my_field1 char(6) not null,
my_field2 char(50) not null,
valid int DEFAULT 1
)
")
| |
|
| >
> I tried creating the procedure to do this, but I have the
> same problem.
>
> The tables already exist at most locations who will
> receive this script. I want to write a universal upgrade
> script that deals with sites no matter what level of the
> DB they have.
>
> I tried doing a create procedure createmytable as
> BEGIN
> create table dbo.mytable(
> myfield char(6) not null,
> valid int DEFAULT 1
> )
>
> END
>
> And it still returned the same error.
>
> Server Message: Number 2714, Severity 16
> Procedure 'createmytable', Line 3:
> There is already an object named 'mytable' in the
> database.
If your table is not too large, your could create it using
dynamic SQL.
Two other options:
Since this appears to be a one time run, concider making the
create table a separate batch. Run it and just let it fail
if the table exists. I know people don't like to see error
messages, but if it is expected <shrug>.
The other is to rename the table temporarily to allow the
proc to be cataloged.
if exists (select name from sysobjects where name =
'myauthors')
begin
exec sp_rename myauthors, tempname
end
go
create proc create_mytable as
create table myauthors (xx varchar(10), aa varchar(10))
go
if exists (select name from sysobjects where name =
'tempname')
begin
exec sp_rename tempname, myauthors
end
go
The check for the table and its possible create should be in
its own batch so that the parser can find the table name for
the other SQL statements.
|
|
|
|
|