問題描述

我試著用谷歌搜索它，但沒有找到方法

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

我有一個(gè) t-sql 腳本，它向表中添加一個(gè)新列，然后根據(jù)同一個(gè)表中的一些其他列用值填充該列，最后刪除一些列.這一切正常.

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.

當(dāng)我想再次運(yùn)行腳本時(shí)出現(xiàn)問題.我有一個(gè) if 子句來檢查缺失的列是否存在，但是即使 if 子句中的代碼沒有運(yùn)行，SSMS 仍然會(huì)抱怨并顯示錯(cuò)誤消息.該腳本必須能夠多次運(yùn)行，而且我不希望顯示錯(cuò)誤消息！

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!

在代碼中(顯然是測試代碼，不想在此處轉(zhuǎn)儲(chǔ)生產(chǎn)代碼...):

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

錯(cuò)誤信息如下:

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

干杯--喬克

推薦答案

是的，沒有動(dòng)態(tài) SQL 也是可能的，但需要一些笨拙的解決方法.我會(huì)為此使用 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 就會(huì)對(duì)查詢執(zhí)行全面檢查."

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

因此，通過在查詢中向不存在的表添加 no-op 引用，可以推遲編譯.通過這種調(diào)整，下面的腳本可以多次運(yùn)行而不會(huì)出錯(cuò).

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

這篇關(guān)于是否可以告訴 SSMS 不檢查 t-sql 腳本中是否存在列?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！