Problem with alter then update in try catch with tran using Transact-SQL

Question

Here is some Transact-SQL I am trying to run via sqlcmd (SQL Server 2005).

USE PUK;
GO
BEGIN TRANSACTION;
BEGIN TRY

   -- - Modify RETRIEVAL_STAT
   alter table dbo.RETRIEVAL_STAT add
      SOURCE nvarchar(10) NULL,
      ACCOUNTNUMBER nvarchar(50) NULL,
      PUK nvarchar(20) NULL;

   -- transform logic.
   update dbo.RETRIEVAL_STAT set
      SOURCE = 'XX',
      ACCOUNTNUMBER = 'XX',
      PUK = 'XX';

END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

I am getting the following error:

(0 rows affected)
Changed database context to 'PUK'.
Msg 207, Level 16, State 1, Server localhost\SQLEXPRESS, Line 11
Invalid column name 'SOURCE'.
Msg 207, Level 16, State 1, Server localhost\SQLEXPRESS, Line 11
Invalid column name 'ACCOUNTNUMBER'.
Msg 207, Level 16, State 1, Server localhost\SQLEXPRESS, Line 11
Invalid column name 'PUK'.

I am guessing that this is because the new columns introduced by the alter statement have not yet been committed, so that the update fails.

My question is how do I get this to work then? I want this to run as a single transaction that I can rollback if something goes wrong.. This is important because I have more alter statements to include yet, and am a bit frustrated that I can't get past this point.

Any assistance would be most appreciated!

Rob :)

Answer 1

Even though I am writing my own answer - all credit goes to @Mikael Eriksson, who suggested that I need to separate different batches with a GO - so that the code that alters the table does not conflict with code that uses the altered table. Thanks Mikael!

USE PUK;
GO
BEGIN TRANSACTION;
BEGIN TRY

   -- - Modify RETRIEVAL_STAT
   alter table dbo.RETRIEVAL_STAT add
      SOURCE nvarchar(10) NULL,
      ACCOUNTNUMBER nvarchar(50) NULL,
      PUK nvarchar(20) NULL;

END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO


USE PUK;
GO
BEGIN TRANSACTION;
BEGIN TRY

    -- transform logic.
   UPDATE      dbo.RETRIEVAL_STAT
   SET         SOURCE = 'ABC',
               ACCOUNTNUMBER = ABC.ACCOUNTNUMBER,
               PUK = ABC.PUK
   FROM        RETRIEVAL_STAT RS
   INNER JOIN  ABC
   ON          RS.SERVICE_NUMBER = ABC.SERVICENUMBER;

   UPDATE      dbo.RETRIEVAL_STAT
   SET         SOURCE = 'DEF',
               ACCOUNTNUMBER = DEF.BILLINGACCOUNTNUMBER ,
               PUK = DEF.PUK
   FROM        RETRIEVAL_STAT RS
   INNER JOIN  DEF
   ON          RS.SERVICE_NUMBER = DEF.SERVICENUMBER;

   UPDATE      dbo.RETRIEVAL_STAT
   SET         SOURCE = 'No Match'
   WHERE       SOURCE IS NULL;

    -- Fix other columns that should be not nullable.
   alter table dbo.RETRIEVAL_STAT
      alter column SERVICE_NUMBER nvarchar (50) NOT NULL;
   alter table dbo.DEF
      alter column PUK nvarchar (20) NOT NULL;


END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

Answer 2

SQL Server checks the columns when the code is compiled. You could use dynamic sql to get around that problem.

-- transform logic.
declare @SQL nvarchar(1000)
set @SQL = 
  'update dbo.RETRIEVAL_STAT set
     SOURCE = @S,
     ACCOUNTNUMBER = @A,
     PUK = @P';

declare @Params nvarchar(1000);
set @Params = N'@S nvarchar(10), @A nvarchar(10), @P nvarchar(20)';

exec sp_executesql @SQL, @Params, N'S', N'A', N'P';