SQL Server Error Handling with TRY CATCH

Understanding TRY CATCH in SQL Server

TRY CATCH is a control-of-flow language construct introduced in SQL Server 2005.

It allows you to execute a block of code (TRY) and catch exceptions in a separate block (CATCH).

This structure helps avoid abrupt termination of scripts and enables custom error handling.

• TRY block contains the code that might cause an error.
• CATCH block executes if an error occurs in the TRY block.
• Error information can be retrieved using built-in functions inside the CATCH block.

Syntax and Basic Example

The basic syntax involves wrapping your SQL statements inside TRY and CATCH blocks.

If an error occurs in TRY, control passes to CATCH where you can handle the error.

Using Error Functions in the CATCH Block

Inside the CATCH block, you can use system functions to get detailed error information.

Common functions include ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), and ERROR_PROCEDURE().

• ERROR_NUMBER(): Returns the error number.
• ERROR_MESSAGE(): Returns the full text of the error message.
• ERROR_SEVERITY(): Returns the severity level.
• ERROR_LINE(): Returns the line number where the error occurred.

TRY CATCH with Transactions

Combining TRY CATCH with transactions helps maintain data consistency.

You can start a transaction in the TRY block and commit it if successful or roll back in the CATCH block if an error occurs.

• Begin transaction before risky operations.
• Commit transaction if no errors.
• Rollback transaction in the CATCH block to undo partial changes.

Best Practices for TRY CATCH Error Handling

Following best practices ensures your error handling is effective and maintainable.

• Always include meaningful error logging inside the CATCH block.
• Avoid empty CATCH blocks that silently swallow errors.
• Use transactions to protect data integrity.
• Test error handling code thoroughly.
• Use THROW to re-raise errors when appropriate.

Common Mistakes to Avoid

Be aware of common pitfalls when using TRY CATCH in SQL Server.

• Not handling errors inside the CATCH block.
• Ignoring transaction state after an error.
• Using RAISERROR without proper severity levels.
• Assuming TRY CATCH catches all errors (some compile-time errors are not caught).