Error Handling in SQL Server

I'm looking into error handling in SQL Server this morning. As of SQL Server 2005, you can use a TRY... CATCH statement that is similar to other .NET languages. There are a number of built-in functions that you can use within the CATCH block. The values produced by these functions persist within the CATCH block and can be accessed as many times as needed. The values revert to NULL outside the CATCH block:

• ERROR_NUMBER() Provides the error number.
• ERROR_SEVERITY() Provides the severity of the error. The severity must exceed 10 in order to be trapped.
• ERROR_STATE() Provides the state code of the error. This refers to the cause of the error.
• ERROR_PROCEDURE() Returns the name of a stored procedure or trigger that caused the error.
• ERROR_LINE() Returns the line number that caused the error.
• ERROR_MESSAGE() Returns the actual text message describing the error.

A simple test of the TRY... CATCH block

The TRY...CATCH can't trap every error. If a database is not available or a table is typed in incorrectly, the batch will simply fail. A TRY...CATCH is valuable when working with transactions. If the transaction in the TRY block fails, it can be rolled back in the CATCH. Here's some code:

Transaction rolled back within TRY...CATCH block

Part 2.1.2 of the TRY block commits an error by trying to update a field with a unique constraint to a value that already exists. If the entire transaction had been successful, the COMMIT statement would have gone through. Since there was an error, the CATCH block is triggered and it gives us a chance to fire a message to the user and rollback the entire transaction.