This is a architectural pattern for handling errors in T-SQL.
Philosophy
The philosophy we will be using for error handling is that we want to make sure that all errors are understood and handled appropriately. This is important for T-SQL because we do not want to corrupt data, stop processing or have silent errors. We will be using TRY CATCH BLOCK to make sure errors are handled appropriately.
Components
- TRY CATCH BLOCK
- “Implements error handling for Transact-SQL that is similar to the exception handling in the Microsoft Visual C# and Microsoft Visual C++ languages. A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.”
- https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql
- THROW
- “Raises an exception and transfers execution to a CATCH block of a TRY…CATCH construct in SQL Server 2016”
- https://docs.microsoft.com/en-us/sql/t-sql/language-elements/throw-transact-sql
- RAISERROR
- “Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct.”
- https://technet.microsoft.com/en-us/library/ms178592(v=sql.105).aspx
- This should be rarely used if ever for new development.
- XACT_ABORT
- “Specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error”
- https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql
- XACT_STATE
- “Is a scalar function that reports the user transaction state of a current running request. XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is capable of being committed.”
- https://docs.microsoft.com/en-us/sql/t-sql/functions/xact-state-transact-sql
- T-SQL Logging
Error Handling Guidelines
- All programmatic T-SQL should be wrapped in a TRY CATCH BLOCK
- We should not be using RAISERROR we should be using THROW
- We should handle transactions correctly in the catch block. We should be rolling back all of the actions unless there is an exception to this rule.
- We should be logging the error using the log framework the error that has occurred in the operations.
- We should make sure that if we are adding error messages that they are clear as to what happened and clear direction to the user of what needed to be fixed.
- If we have a common problem that is correctable in the CATCH BLOCK we should be implementing that correction and trying again.
- We should be reviewing as a team common error messages and adding them to sys.messages on server instances.
Error Handling Template
I have published an example stored procedure to Git Hub that can be used when starting a new stored procedure or any T-SQL code to understand how error handling should work.
GitHub
https://github.com/DerekHackett/T-SQL-ErrorHandling
ERROR flickr photo by sisssou shared under a Creative Commons (BY) license