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

Question

This is what Transact-SQL I am trying to run through 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 get 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 assume that this is because the new columns introduced by the alter operator have not yet been completed, so the update does not work.

My question is: how do I get this to work? I want this to be executed as one transaction, which I can rollback if something goes wrong. This is important because I have a few more addition sentences that need to be included, but I'm a little disappointed that I can't get past this point.

Any help would be most appreciated!

Rob :)

Answer 1

Despite the fact that I write my own answer - it all depends on @Mikael Eriksson, who suggested that I need to separate different parties with GO - so that the code that changed the table does not contradict the code that uses the modified 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 validates columns when compiling code. You can use dynamic sql to get around this 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';