sap-ase - 如果存在非空或默认值的更改表语句不起作用

Question

我尝试添加一个新列 第2列进 test_tbl 并将列设置为默认值 “不适用”和 不为空 .声明如下:

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

错误是

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

但是如果我添加一列 可以为空 .

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

它可以工作。我对这些很困惑。
我搜索了一些标签，知道动态 sql 可以工作。

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.

在 Sybase DOC 中关于 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.

我想知道为什么可空列语句可以无错误地执行!

Answer 1

我在 Sybase ASE 15 上看到了相同的行为

除了您已经从 Sybase Documentaiton 中引用的内容之外，我无法为您提供解释，但是您可以通过将调用 alter table 包装在 execute() 语句中来编写一致的行为，如下所示

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

我的猜测是服务器能够评估 if...else在执行alter 语句之前在此实例中使用语句。