Skip to content

Databases

Error Handling in SQL Server: Best Practices

For any application, data is the most valuable asset which must be organized and handled with due care. With the advent of advanced analytics it has become a thing of utmost importance to keep track of not only the successful transaction but also to track and log errors that crop up when a user tries to update/insert some information to the database. In most of the cases, an error some where in the database results in a lost opportunity to the business or an inconsistent record in the database. It makes sense for the developer to get errors logged and analyse them carefully and take corrective measures to minimize the errors.

Tracking and Logging database errors can help you to manage your database efficiently. It helps the development team to analyze these errors, act on them and fix them. The long term goal should be to reduce most of the avoidable errors.

TIP – Never show any cryptic message to your end user. Message shown to end users should always be generic and ‘user friendly’. In brief, keep scary technical details of the error for your reference only by logging them to a table.

MS SQL Server (2005 onward) has robust error handling mechanism with the introduction of ‘TRY – CATCH’ construct (introduced in SQL Server 2005). Even after 8+ years of introduction, practically I’ve seen many SQL developers either don’t use it or use it in a wrong way. Many of those who are not using it are the developers who started writing SQL procedure since SQL Server 2000. In those days only @@Error was available for error handling – quite messy and primitive! In contrast ‘Try – Catch’ is quite neat, more informative and far more simple and efficient way of error handling. Moreover, @@ERROR is raised only for errors, not for warnings.

To see TRY – CATCH construct in action, try to execute the following query in SQL SERVER 2005 / 2008 / 2012.

Error-SQL

 

 

 

 

 

 

The result will be something like:

ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
8134 16 1 null 3 Divide by zero error encountered.

 

What’s the Best and Simple Way of Handling Errors in Stored Procedures?

  1. Create a table to log every Stored Procedure related errors.
  2. Create a generic stored procedure for error handling.
  3. Use ‘TRY – CATCH’ construct and invoke (call) the generic Stored Procedure created at step 2 from every other stored procedure’s CATCH block [where ever you feel the necessity for error handling]

Step (i) code follows:

Create_Table

Once you execute the above it will create a table named DbErrorLog.

Step (ii) code follows:

Create_SP

Once you execute the above code it will create a Stored Procedure that can be called from any other Stored Procedure for error handling. As you can see above, it expects an input parameter @ErrorProc i.e. the name of the procedure from where this procedure is getting called. The following stored procedure shows how to implement the stored procedure you just created for error handling.

Step (iii) code follows:

Implmentation

Note:

In above snippet:

1. sql_statement =  Any T-SQL statement.

2. statement_block = A group of T-SQL statements in a batch or enclosed in a BEGIN…END block.

Got a query? Please share it in the comment section. Happy Coding!

Be Sociable, Share!
    The following two tabs change content below.

    Basant Singh

    Basant is a talented software developer who is very engaged in web & cloud platforms on a daily basis. He is a contributor to codingthis.com reporting on various news and troubleshooting on various platforms. Basant is very strong in SQL and a great asset to the CodingThis community.

    Latest posts by Basant Singh (see all)