How to make 2 queries execute as one-CodePudding

I have 2 queries that i want to execute as one

ALTER TABLE X ADD Y INT

UPDATE X SET Y = Z

When i select them both to execute shows

Invalid column name Y

CodePudding user response：

This is a compiler error. Both the statements are valid, however, the entire batch is compiled first, prior to any execution. As a result, the entire batch fails as before the statements are executed the column Y does not exist in the table X. Though the compiler is "clever" enough to defer the validation for some objects (for example you can CREATE and then INSERT into a TABLE in the same batch) this is not true for things like when you ALTER a TABLE to add a COLUMN.

The "simple" solution would be to put the 2 statements into 2 batches. In an IDE-like application such as SSMS or ADS, you can use GO to separate your statements:

ALTER TABLE X ADD Y INT NULL;
GO
UPDATE X SET Y = Z;

If they must be in the same batch, you can defer the compilation of the second statement. One method would be to execute the statement with sys.sp_executesql. This would mean that the statement executed by the procedure would not be compiled until sys.sp_executesql itself is executed; which is after the table was ALTERed:

ALTER TABLE X ADD Y INT NULL;
EXEC sys.sp_executesql N'UPDATE X SET Y = Z;';