database - SQL: Try/Catch doesn't catch an error when attempting to access a table that it can't find

Question

I've created a stored procedure that runs a number of commands to modify data. I only want to commit the transaction if everything succeeds. I'm doing this by using a try-catch block in the manner below (where my CATCH block in the real thing uses RAISERROR to return error messages):

BEGIN TRY
  BEGIN TRANSACTION
  UPDATE Table1 SET MyVarcharColumn = 'test'
  UPDATE Table2 SET MyBitColumn = 1
  UPDATE Table3 SET MyIntColumn = 42
  COMMIT TRANSACTION
END TRY
CATCH
  ROLLBACK TRANSACTION
END CATCH

That works the way I want it to. If, for example, I set MyBitColumn to 'b' instead of 1, the error is caught, control flows to the CATCH, and the transaction is not commited.

One issue I've noticed is that if, say, Table3 does not exist in the database then it errors out (invalid object name), but the CATCH block is never executed and the transaction remains open.

I want to handle this to take care of any (remote) possibility that a database gets modified (or something happens where this stored procedure is added properly, but one of the tables isn't).

How should I handle these error cases?

-Thanks for any help.

Answer 1

At the start of your script use SET XACT_ABORT

SET XACT_ABORT ON

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

I don't think that's going to be possible:

The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

• Compile errors, such as syntax errors, that prevent a batch from running.

• Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

Ref.

The following example shows how an object name resolution error generated by a SELECT statement is not caught by the TRY…CATCH construct, but is caught by the CATCH block when the same SELECT statement is executed inside a stored procedure.

USE AdventureWorks2012;
GO

BEGIN TRY
    -- Table does not exist; object name resolution
    -- error not caught.
    SELECT * FROM NonexistentTable;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

The error is not caught and control passes out of the TRY…CATCH construct to the next higher level.