Transact-SQL - how to prevent the start of the second batch if the first crashes?

Question

In the previous question, Problem with alter, and then update in try catch with tran, using Transact-SQL , I found out that in order to make SQL work, I had to break this down to two batches. Here is the SQL for this:

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; --- ... snip 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 

Now my question is: how can I prevent the launch of the second batch if the first batch failed?

Thanks for any advice!

Rob :)

Answer 1

Following the additional information in your comment, the problem you are facing is that this is not an exciting bug.

The parsing of the first batch failed due to a nonexistent object, so the entire batch was not completed (including the raiserror operator).

to give a simplified example

 SELECT * FROM NonExistentTable GO SELECT 1 AS [SecondBatch] 

Returns

 Msg 208, Level 16, State 1, Line 3 Invalid object name 'NonExistentTable'. SecondBatch ----------- 1 

A few ways around this is to put each batch in a child region using dynamic SQL.

 BEGIN TRY EXEC('SELECT * FROM NonExistentTable') END TRY BEGIN CATCH RAISERROR('Oh no a fatal error', 20, -1) WITH LOG END CATCH EXEC('SELECT 1 AS [SecondBatch]') 

Returns

 Msg 2745, Level 16, State 2, Line 5 Process ID 55 has raised user error 50000, severity 20. SQL Server is terminating this process. Msg 50000, Level 20, State 1, Line 5 Oh no a fatal error Msg 0, Level 20, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded. 

Or (since variables will not be available between batches), you could SET CONTEXT_INFO at the end of each batch, and then check this at the beginning of the next batch.

 BEGIN TRY SELECT * FROM NonExistentTable SET CONTEXT_INFO 1 END TRY BEGIN CATCH END CATCH GO IF CONTEXT_INFO() <> 1 RETURN BEGIN TRY SELECT 1 AS [SecondBatch] SET CONTEXT_INFO 2 END TRY BEGIN CATCH END CATCH GO IF CONTEXT_INFO() <> 2 RETURN BEGIN TRY SELECT 1 AS [ThirdBatch] SET CONTEXT_INFO 3 END TRY BEGIN CATCH END CATCH