Menu
We are currently on SQL 2005 at work and I am migrating an old Foxpro system to new web application backed by SQL Server. I am using TRY CATCH in T-SQL for transaction processing and it seems to be working very well. One of the other programmers at work was worried about this as he said he had heard of issues where the catch phrase did not always catch the error. I have beat the sproc to death and cannot get it to fail (miss a catch) and the only issues I have found searching around the net is that it will not return the correct error number for error numbers < 5000. Has anyone experienced any other issues with TRY CATCH in T-SQL - especially if it misses a catch? Thanks any input you may wish to provide.
260
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.
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.
If the stored procedure contains a TRY... CATCH construct, the error transfers control to the CATCH block in the stored procedure. When the CATCH block code finishes, control is passed back to the statement immediately after the EXECUTE statement that called the stored procedure.
Option A is the correct choice. It is possible for all statements in a transaction to work and then the actual COMMIT to fail, so you keep the COMMIT inside your TRY block so that any failure of the COMMIT will be caught and you can gracefully handle this error and rollback.
TRY ... CATCH doesn't catch every possible error but the ones not caught are well documented in BOL Errors Unaffected by a TRY…CATCH Construct
TRY…CATCH constructs do not trap the following conditions:
- Warnings or informational messages that have a severity of 10 or lower.
- Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error.
- Attentions, such as client-interrupt requests or broken client connections.
- When the session is ended by a system administrator by using the KILL statement.
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.
These errors are returned to the level that ran the batch, stored procedure, or trigger.
161
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
