title: Add column to table and then update it inside transaction
body: <p>I am creating a script that will be run in a MS SQL server. This script will run multiple statements and needs to be transactional, if one of the statement fails the overall execution is stopped and any changes are rolled back.</p>
  
  <p>I am having trouble creating this transactional model when issuing ALTER TABLE statements to add columns to a table and then updating the newly added column. In order to access the newly added column right away, I use a GO command to execute the ALTER TABLE statement, and then call my UPDATE statement. The problem I am facing is that I cannot issue a GO command inside an IF statement. The IF statement is important within my transactional model. This is a sample code of the script I am trying to run. Also notice that issuing a  GO command, will discard the @errorCode variable, and will need to be declared down in the code before being used (This is not in the code below).</p>
  
  <pre><code>BEGIN TRANSACTION
  
  DECLARE @errorCode INT
  SET @errorCode = @@ERROR
  
  -- **********************************
  -- * Settings
  -- **********************************
  IF @errorCode = 0
  BEGIN
   BEGIN TRY
    ALTER TABLE Color ADD [CodeID] [uniqueidentifier] NOT NULL DEFAULT ('{00000000-0000-0000-0000-000000000000}')
    GO
   END TRY
   BEGIN CATCH
    SET @errorCode = @@ERROR
   END CATCH
  END
  
  IF @errorCode = 0
  BEGIN
   BEGIN TRY
    UPDATE Color
    SET CodeID= 'B6D266DC-B305-4153-A7AB-9109962255FC'
    WHERE [Name] = 'Red'
   END TRY
   BEGIN CATCH
    SET @errorCode = @@ERROR
   END CATCH
  END
  
  -- **********************************
  -- * Check @errorCode to issue a COMMIT or a ROLLBACK
  -- **********************************
  IF @errorCode = 0
  BEGIN
   COMMIT
   PRINT 'Success'
  END
  ELSE 
  BEGIN
   ROLLBACK
   PRINT 'Failure'
  END
  </code></pre>
  
  <p>So what I would like to know is how to go around this problem, issuing ALTER TABLE statements to add a column and then updating that column, all within a script executing as a transactional unit.</p>