if exists alter table statement with not null or default value doesn't work
I try to add one new column column2 into the test_tbl and set the column with default value 'N/A' and not null . The statement is following:
if not exists (select 1 from syscolumns where object_name(id) = 'test_tbl' and name = 'column2')
begin
alter table test_tbl add column2 varchar(20) default 'N/A' not null
end
The error is
Could not execute statement.
Column names in each table must be unique. Column name 'column2' in table 'test_tbl' is specified more than once.
Sybase error code=2705
Severity Level=16, State=3, Transaction State=1
Line 4
But if I add one column which is nullable .
if not exists (select 1 from syscolumns where object_name(id) = 'test_tbl' and name = 'column2')
begin
alter table test_tbl add column2 varchar(20) null
end
It could work. I'm so comfused with these. I searched for some tags and know that the dynamic sql could work.
The error is being raised during normalizations (as the parse tree is being converted into a normalized query tree) rather than at execution. The contents of dynamic sql aren't processed until they are actually called, avoiding the error.
In Sybase DOC about if...else
When an alter table, create table, or create view command occurs within an if...else block, Adaptive Server creates the schema for the table or view before determining whether the condition is true. This may lead to errors if the table or view already exists.
I want to know why the nullable column statement could execute without error!
I see the same behaviour on Sybase ASE 15
I can't provide you with an explanation beyond what you have already quoted from the Sybase Documentaiton, however you can script a consistent behaviour by wrapping the call to alter table in an execute() statement as follows
if not exists (select 1 from syscolumns where object_name(id) = 'tbl_test' and name = 'column2')
begin
execute("
alter table tbl_test add column2 varchar(20) default 'N/A' not null
")
end
My guess is that the server is able to evaluate the if...else statement in this instance before executing the alter statement.
You should write go..
Please check below code it's working fine for me
IF EXISTS ( SELECT 1 FROM sysobjects WHERE name = 'a_table' AND type = 'U' )
drop table a_table
go
CREATE TABLE a_table ( col1 int not null,col2 int null )
go
上一篇: 在JavaScript中是空的对象？