Error handling using TRY Catch was introduced in SQL Server 2005. If you have .net background then you must be familiar of using TRY – CATCH to handle errors. It is used in pretty much same was as in .Net. An example is shown below.

The above query inserts a duplicate value in Person. Address Type table. This results in unique key constraint violation error, which is captured by CATCH block as shown in below snapshot.

1_error handling using try catch in sql server

If you would look into Adventure Works 2014 database there is a procedure usp log Error which logs error into Error log table. The procedure text from the database is given below

The procedure logs error into Error log table when transaction is in commit table state else it displays a message. The procedure also prints the error using usp Print Error stored procedure. Let’s use this procedure to log an error in error log table.

2_error handling using try catch in sql server

As shown in above snapshot, usp Log Error procedure is executed in catch block, which inserts a row for the error in Error Log table.

Another use case for TRY-CATCH is re-running transactions involved in a deadlock. This is done by checking the error number for deadlock and then rerunning the transaction. The details can be found here

More information regarding TRY-CATCH can be found here

 

Regards

Ahmad Osama

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook