Alter statement in a Transaction

Question

I execute the code below:

use AdventureWorks2008R2
begin transaction
BEGIN
alter table HumanResources.Department add newcolumn int
update HumanResources.Department set newcolumn=1 where departmentid=1
END
commit

The error I get is:

Invalid column name 'newcolumn'.

Can ALTER statements be included in Transactions like this? If so, how can I prevent this error?

I have researched this online e.g. here. I have not found an answer to my specific question.

Answer 1

Yes, you can include an ALTER in a transaction. The problem is that the parser validates the syntax for your UPDATE statement, and can't "see" that you are also performing an ALTER. One workaround is to use dynamic SQL, so that the parser doesn't inspect your syntax (and validate column names) until runtime, where the ALTER will have already happened:

BEGIN TRANSACTION;

  ALTER TABLE HumanResources.Department ADD newcolumn INT;

  EXEC sp_executesql N'UPDATE HumanResources.Department 
    SET newcolumn = 1 WHERE DepartmentID = 1;';

COMMIT TRANSACTION;

Note that indentation makes code blocks much more easily identifiable (and your BEGIN/END was superfluous).

Answer 2

Aaron has explained everything already. Another alternative that works for ad-hoc scripts in SSMS is to insert the batch separator GO so that the script is sent as two parts to the server. This only works if it is valid to split the script in the first place (you can't split an IF body for example).