Hello and welcome once again. Recently I was following Brent Ozar tweet and proudly discovered myself standing tall with the majority! So, I re-cap my error handling skill and also did a further study on error handling tips and tricks. I would like to share with you.
Error Handling
The prerequisite of a reliable application is bug free and error handling. At application development time, usually, developers take care of exception and error handling. Similarly, error and exception handling should be taken care while designing the database especially like stored procedure. In rest of the writing, we will take a closer look at the TRY… CATCH statement: the syntax, how it looks and what can be done when an error occurs.
TRY… CATCH
Let's examine the syntax which is similar to standard programming language error handling syntax. Any exception/error in TRY block will be trapped into CATCH block. In CATCH block, the trapped error can be resolved, or raised.
Syntax
BEGIN TRY
-- Any TSQL statement
END TRY
BEGIN CATCH
-- Any TSQL statement specially to fix, logged or raise the error
END CATCH
[ ; ]
Try Block
In try block we probe for exception/error in TSQL statements. Usually, the regular flow of our TSQL statements with our application logic are put here.
Catch Block
We are only in catch block, in case there is any exception/error is trapped in Try block. Usually, the error fixing/logging or further error escalation TSQL statements are resides here.
More about the Error
In catch block, some system functions can be used to retrieve more information about the exception. For example,
- ERROR_NUMBER() - returns error number
- ERROR_SEVERITY() - returns the severity of error
- ERROR_STATE() - returns the error state number
- ERROR_PROCEDURE() - returns the name of the stored procedure or trigger where the error occurred
- ERROR_LINE() - returns the line number inside the routine that caused the error.
- ERROR_MESSAGE() - returns the complete text of the error message
Important note:- Out side of CATCH block the above function will return NULL.
Jump to the Example
BEGIN TRY
-- Generate a divide-by-zero error
SELECT
0 / 0 AS Result;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_STATE() AS ErrorState,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
Error Escalation by THROW
An caught error can be escalated further by throwing it.
THROW [ { error_number | @local_variable },
{ message | @local_variable },
{ state | @local_variable } ]
[ ; ]
BEGIN TRY
-- Generate a divide-by-zero error
SELECT
0 / 0 AS Result;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_STATE() AS ErrorState,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
Throw; -- This will re-throw the original caught exception again
END CATCH;
GO
Errors Unaffected by a TRY...CATCH
- 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.
- Object name resolution errors
Final Thoughts
SQL Server makes error handling very easy. Let's utilize the feature for sake of quality.