Is it possible to tell SSMS not to check if a column exists in the t-sql script?

Question

I tried to do this but couldn't find a way

I have a t-sql script that adds a new column to a table, then populates these columns with values ​​depending on some other columns in the same table and finally deletes some columns. All of this works great.

The problem occurs when I want to run the script again. I have an if clause that checks for missing columns, but SSMS still complains and displays an error message even if the code inside the if clause is not running. The script should be able to run more than once, and I don’t want the error messages to be displayed!

In code (obviously, test code, no need to unload 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'. 

Greetings --Jocke

Answer 1

Yes, this is possible without dynamic SQL, but with a little kludgey workaround. I would just use EXEC for this.

This explains the behavior in SQL 2000

Erland Sommarskog mentions "as soon as all the tables in the query exist, SQL Server performs full query checks."

Thus, by adding a no-op link to a query on a table that does not exist, compilation can be delayed. With this setting, the script below can be run several times without receiving an 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 

Answer 2

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

Answer 3

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

In addition, it looks like you have the correct GOs, but I found that if I hadn't been GO after checking to add a column, then the script would not even resume.