是否可以告诉 SSMS 不检查 t-sql 脚本中是否存在列?

2021-09-10 00:00:00 sql tsql sql-server

我试着用谷歌搜索它，但没有找到方法

I tried to google it, but din't find a way

我有一个 t-sql 脚本，它向表中添加一个新列，然后根据同一个表中的一些其他列用值填充该列，最后删除一些列.这一切正常.

I have a t-sql script that adds a new column to a table, then fills that columns with values depending on some other columns in the same table and finally removes some columns. This all works fine.

当我想再次运行脚本时出现问题.我有一个 if 子句来检查缺失的列是否存在，但是即使 if 子句中的代码没有运行，SSMS 仍然会抱怨并显示错误消息.该脚本必须能够多次运行，而且我不希望显示错误消息！

The problem occures when I want to run the script again. I have a if clause that checks if the missing columns exists, but SSMS still complains and displays error messaged even though the code inside the if clause if not run. The script must be able to run more then once, and I don't want the error messages to be displayed!

在代码中(显然是测试代码，不想在此处转储生产代码...):

In code (obviously test code, don't want to dump production code here...):

create table test (
 Name text,
 Switch int,
 ValueA int,
 ValueB int)
go

insert into test values ('Name', 0, 5, 10)

if not exists (select 1 from INFORMATION_SCHEMA.COLUMNS
      where COLUMN_NAME = 'ValueC' and TABLE_NAME = 'test')
begin
 alter table test
 add ValueC int
end
go

-- This batch rasies error when run more then once!
if exists (select 1 from INFORMATION_SCHEMA.COLUMNS
     where COLUMN_NAME = 'ValueA' and TABLE_NAME = 'test')
begin
 update test
 set ValueC = (select case Switch
      when 0 then (select (ValueA - ValueB))
      when 1 then (select (ValueB - ValueA))
     end)
end
go

if exists (select 1 from INFORMATION_SCHEMA.COLUMNS
     where COLUMN_NAME = 'ValueA' and TABLE_NAME = 'test')
begin
 alter table test drop column ValueA
end
go

select * from test
--Name 0 10 -5

错误信息如下:

Msg 207, Level 16, State 1, Line 6
Invalid column name 'ValueA'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'ValueA'.

干杯--乔克

推荐答案

是的，没有动态 SQL 也是可能的，但需要一些笨拙的解决方法.我会为此使用 EXEC.

Yes it is possible without dynamic SQL but with a bit of a kludgey workaround. I would just use EXEC for this.

SQL 2000 中的行为 在此处解释

The behaviour in SQL 2000 is explained here

Erland Sommarskog 提到一旦查询中的所有表都存在，SQL Server 就会对查询执行全面检查."

Erland Sommarskog mentions "once all tables in a query exist, SQL Server performs full checks on the query."

因此，通过在查询中向不存在的表添加 no-op 引用，可以推迟编译.通过这种调整，下面的脚本可以多次运行而不会出错.

So by adding a no-op reference in the query to a table that doesn't exist compilation can be deferred. With this adjustment the script below can be run multiple times without getting the error.

insert into test values ('Name', 0, 5, 10)

if not exists (select 1 from INFORMATION_SCHEMA.COLUMNS
      where COLUMN_NAME = 'ValueC' and TABLE_NAME = 'test')
begin
 alter table test
 add ValueC int
end
go

create table #dummy
(i int)

-- This batch raised error when run more then once!
if exists (select 1 from INFORMATION_SCHEMA.COLUMNS
     where COLUMN_NAME = 'ValueA' and TABLE_NAME = 'test')
begin
 update test
 set ValueC = (select case Switch
      when 0 then (select (ValueA - ValueB))
      when 1 then (select (ValueB - ValueA))
     end) where not exists(select * from #dummy)
end

drop table #dummy
go


if exists (select 1 from INFORMATION_SCHEMA.COLUMNS
     where COLUMN_NAME = 'ValueA' and TABLE_NAME = 'test')
begin
 alter table test drop column ValueA
end



go


select * from test
--Name 0 10 -5

相关文章