Implement error handling

May include but is not limited to: TRY/CATCH, RAISERROR, retrieving error information, custom error messages, @ERROR.

Unfortunately, we cannot write bullet-proof code in any language, T-SQL included. Fortunately, most languages provide ways to deal with errors within your applications, T-SQL included (at least, Microsoft’s implementation).

The easiest but most limited way to deal with errors is to query the @@ERROR global variable. If any error occurs, @@ERRROR returns its number. If not, it returns 0. The severe limitation is that it resets its value after each successful statement. And a simple SELECT @@ERROR counts as a successfully executed statement, so you won’t be able to pass the error information any further.

But I went too far. Let’s see what builds up an error in SQL Server:

  • Error number: a unique number which identifies the current error message. SQL Server reserves the error numbers 0-50000, so you can build custom errors from number 50001.
  • Severity level: from 0 to 25, indicates the severity of what has happened.
  • Error message: a textual representation of the error.

You can write your own custom error messages by using the sp_addmessage stored procedure, and adding the error information as parameters. First you need to write an error message in English, than you can translate it to other languages by specifying the @lang parameter to sp_addmessage.

@@ERROR isn’t the only possible way of dealing with exceptions. SQL Server introduces the well-known TRY/CATCH blocks. You write statements in a TRY block, and if an error occurs (with a severity level between 11 and 19) the CATCH block executes. It is important that severity levels below 11 are informational messages, which are not getting caught, so execution of the TRY section continues. Severity levels above 19 are considered as fatal errors, and they are returned immediately to the caller.

To throw errors, you can use the RAISERROR function, specifying the message, severity level, error number, etc. RAISERROR is particularly useful in CATCH blocks, to rethrow an exception to the caller.

Another thing to mention is the SET XACT_ABORT ON clause. SQL Server doesn’t roll back a transaction which has an error. By specifying SET XACT_ABORT ON you override this behavior, so when an error happens in a transaction, and there are no TRY/CATCH blocks, it will be rolled back. If there are, execution switches to the CATCH block, where you can only roll it back, unfortunately.

Advertisements

Tags: , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: