if exists alter table statement with not null or default value doesn't work

Question

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!

Answer 1

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.