Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 959
Luke Avatar asked Feb 22 '13 00:02

Luke


People also ask

Which errors Cannot be handled by catch block in SQL Server?

CATCH Construct section) some errors are not caught by CATCH statement. Particularly: 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.

How do I catch an error message in SQL Server?

When called in a CATCH block, ERROR_MESSAGE returns the complete text of the error message that caused the CATCH block to run. The text includes the values supplied for any substitutable parameters - for example, lengths, object names, or times. ERROR_MESSAGE returns NULL when called outside the scope of a CATCH block.

How do you try catch in SQL?

A TRY... CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection. A TRY block must be immediately followed by an associated CATCH block. Including any other statements between the END TRY and BEGIN CATCH statements generates a syntax error.

Can we use try catch in SQL functions?

Note that you cannot use TRY... CATCH blocks inside T-SQL UDFs. If you have to capture errors that occur inside a UDF, you can do that in the calling procedure or code.


1 Answers

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.

like image 131
Mitch Wheat Avatar answered Oct 12 '22 02:10

Mitch Wheat