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

Question

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,
      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 :)

possible duplicate of [SQL Server - stop or break execution of a SQL script](http://stackoverflow.com/questions/659188/sql-server-stop-or-break-execution-of-a-sql-script) — Martin Smith, Sep 17 '11 at 09:51
@Martin Smith Ok sweet **raiserror('Oh no a fatal error', 20, -1) with log** does the job nicely. Thanks. — Robert Mark Bram, Sep 18 '11 at 11:47
@Martin Smith On second thought, I am still missing some information: how do I detect the error condition? In the example above, I tried changing the alter table's name and put RAISERROR in the catch.. it didn't work. It told me **Cannot find the object "dbo.RETRIEVAL_SdTAT" because it does not exist or you do not have permissions.** and happily continued running the rest of the SQL. — Robert Mark Bram, Sep 18 '11 at 11:56
Have you tried this answer? http://stackoverflow.com/questions/659188/sql-server-stop-or-break-execution-of-a-sql-script/2590364#2590364. It looks like it does what you want but requires you to use sqlcmd or sqlcmd mode in SSMS. — Mikael Eriksson, Sep 19 '11 at 06:08

score 1 · Accepted Answer · answered Sep 18 '11 at 12:08

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

"not a catchable error" - is that because it breaks compilation? What defines a catchable error? I think @Mikael Eriksson had a simple suggestion that worked: :on error exit — Robert Mark Bram, Sep 19 '11 at 23:43
See [Errors Unaffected by a TRY…CATCH Construct](http://msdn.microsoft.com/en-us/library/ms175976.aspx) — Martin Smith, Sep 19 '11 at 23:44

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

1 Answers1

Linked