Transact-SQL - how to prevent second batch from running if the first failed?

In a previous question, Problem with alter then update in try catch with tran using Transact-SQL, I found out that to get my SQL working I had to break it up into two batches. Here is the SQL for that:

USE PUK;
GO
BEGIN TRANSACTION;
BEGIN TRY

   -- - Modify RETRIEVAL_STAT
   alter table dbo.RETRIEVAL_STAT add
      SOURCE nvarchar(10) NULL,
      ACCOUNTNUMBER nvarchar(50) NULL,
开发者_StackOverflow社区      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

My question now is: how do I prevent the second batch from running if the first batch fails?

Thanks for any advice!

Rob :)

Following the additional information in your comment the problem you are having is because this is not a catchable error.

The parsing of the first batch failed due to a non existent object so the whole batch failed to execute (including your raiserror statement).

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 couple of ways around this would be to put each batch into a child scope 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 (as variables will be out of scope between batches) you could SET CONTEXT_INFO at the end of each batch and then check this at the start 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