T-SQL Error Handling

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

1. TRY CATCH BLOCK
   1. “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.”
   2. https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql
2. THROW
   1. “Raises an exception and transfers execution to a CATCH block of a TRY…CATCH construct in SQL Server 2016”
   2. https://docs.microsoft.com/en-us/sql/t-sql/language-elements/throw-transact-sql
3. RAISERROR
   1. “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.”
   2. https://technet.microsoft.com/en-us/library/ms178592(v=sql.105).aspx
   3. This should be rarely used if ever for new development.
4. XACT_ABORT
   1. “Specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error”
   2. https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql
5. XACT_STATE
   1. “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.”
   2. https://docs.microsoft.com/en-us/sql/t-sql/functions/xact-state-transact-sql
6. T-SQL Logging
   1. https://derekhackett.com/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