SQL Server 'Invalid column name' in transaction

Question

I have a script where I'm adding a column to the table, and immediately after I populate that column with data from another table. I'm getting 'Invalid column name' error on the column that I am adding.

The error, specifically, is Invalid column name 'tagID'.

The code between BEGIN TRANSACTION and COMMIT is actually an excerpt of a much larger script, but this is the relevant excerpt (and I need all of it to succeed or simply roll back):

BEGIN TRY
BEGIN TRANSACTION
  ALTER TABLE [Items] ADD tagID [uniqueidentifier] NULL

  MERGE INTO
    Items AS target
  USING
    Tags AS t ON t.tag = target.tag
  WHEN MATCHED THEN
    UPDATE SET target.tagID = t.id;
COMMIT
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION
END CATCH
GO

Answer 1

SQL Server tries to compile all statements in the batch. If the table doesn't exist compilation of the statement is deferred but there is no deferred compilation for missing columns.

You can use

BEGIN TRY
BEGIN TRANSACTION
  ALTER TABLE [Items] ADD tagID [uniqueidentifier] NULL
  EXEC('
  MERGE INTO
    Items AS target
  USING
    Tags AS t ON t.tag = target.tag
  WHEN MATCHED THEN
    UPDATE SET target.tagID = t.id;
 ')
COMMIT
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION
END CATCH
GO

To push the usage of the column into a child batch compiled after the column is created. It still belongs to the same transaction opened in the parent scope.