是否有可能告诉SSMS不要检查t-sql脚本中是否存在列？

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

我试图谷歌它，但没有找到办法

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.

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

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!

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

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

Here is the error message:

这是错误消息：

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

Cheers --Jocke

干杯 - 乔克

3 个解决方案

#1

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

是的，没有动态SQL，但有一些kludgey解决方法是可能的。我会用EXEC来做这件事。

The behaviour in SQL 2000 is explained here

这里解释了SQL 2000中的行为

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

Erland Sommarskog提到“一旦查询中存在所有表，SQL Server就会对查询执行全面检查。”

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

#2

why don't you jsut use a temp table or variable table, add the last column to the declaration, and then you wouldn't have this problem?

为什么不jsut使用临时表或变量表，将最后一列添加到声明中，然后你不会有这个问题？

#3

I had this exact problem and the only thing that worked for me was to save the script. Close it. Then open it again in and run it in the query window.

我有这个确切的问题，唯一对我有用的是保存脚本。关闭它。然后再次打开它并在查询窗口中运行它。

Also, it looks like you have the proper GOs, but I found that if I was missing the GO after the check to add the column then not even re-opening the script worked.

此外，看起来你有正确的GO，但我发现，如果我在检查后错过GO以添加列，那么甚至没有重新打开脚本工作。

#1