Error-handling and error messages

This tutorial will explain what to do when your SQL query goes pear-shaped!

What is an Error?

Let's create an error!  Consider the SQL excerpt below, which tries to squeeze a quart into a pint pot (that English expression may not travel well ...).

When you run this, you'll see this error:

You can't store text in an integer variable!

When you double-click on the error message, SQL will highlight the offending line:

Double-click on any error message to highlight the statement which caused the error.

Error information captured

For any error, SQL Server stores the following bits of information:

Trapping errors using TRY ... CATCH

Other languages allow you to trap errors - here are a couple of examples:

Here's how to do this in SQL:

There are two important things to notice about this syntax:

1. There is no FINALLY clause in SQL (although if you don't program in other languages, you won't miss it!).

2. You can't omit the CATCH clause, even if you don't need or want one!

Here's a simple example of the use of error-handling:

This would show the following output when run:

Having failed to store owl in the variable, we successfully store 1 instead.

It's time now to look at the various parts of an error in more detail, then I'll give some practical examples of error-handling in SQL.

There are 6 standard error functions in SQL - each is described in more detail below.

The Error Functions in SQL

The table below uses the following error message as an example:

This page uses this error message as its example.

Here's the 6 bits of information displayed for any error such as this:

You could use these to trap the above error and show it in a glossier form.  For example, suppose you ran the following query:

This would produce the following output:

You can see the various bits of information about the error more clearly here.

Built-in error messages

If you want to see what error messages are installed with SQL Server, run the following script:

On my version of SQL Server 2022 this gives 345,004 possible error messages (this number seems to go up with each new version of SQL Server released). I'm not sure if this means that the later version can generate more errors! 

Here are the first few error messages for SQL Server 2022:

The Error Number

On my instance of SQL Server the highest-numbered error is 49,902.  When you create your own errors, they are automatically flagged as number 50,000 by default:

Here the web has gone down, so we raise an error to reflect this. Because we haven't given an error number, 50000 is assumed.

There's more detail on raising your own error messages at the end of this blog series, including using the new THROW command.

The Error Severity

The severity number goes from 0 to 25.  Here's what the numbers mean:

The Error State

There's not a great deal to say about this.  It's provided as a way of distinguishing between two messages with identical number.  To all intents and purposes it always takes the value 1, and is of no interest.  If you want to prove this to yourself, have a look at Microsoft's take on the subject.

The Error Procedure

This gives the name of the procedure or trigger in which the error occurred, where there is one (otherwise, it returns Null).  What follows is an example to illustrate where this might be useful. 

First create a stored procedure which takes an integer parameter, then tries to divide it by 0:

Now write a main procedure which calls this subprocedure, passing the number 1 to it:

When you run this main procedure, it will crash.  Here's what the error-trapping will show:

The error occurred in the spSub procedure, but is reported from spMain.

The Error Line Number

You can display line numbers for SQL in SQL Server by selecting Tools --> Options, then ticking the box shown here:

Tick the Line numbers box in the Transact-SQL General tab.

You can then use SQL line numbers to see where an error occurred:

Here the error occurred on line number 7.

The Error Message

The text of error messages is the most useful part of an error, but there's not much more to say about it here!

Now that we've covered the parts of an error message, let's have a look at a practical example showing the use of error-handling in SQL.

This page gives a practical example of using TRY / CATCH, which uses the following two tables:

There are 3 authors ...	... and 5 books.

You can run the script to create this database by downloading and opening this file in SQL Server Management Studio.

What our stored procedure should do

Suppose that we want to create a stored procedure to allow us to add a book easily.  We could use this to add books for existing authors:

Alternatively, we might try to add books whose authors don't yet exist:

Here's what we should see for these two cases:

If the author exists ...	... and if the author doesn't.

Building the stored procedure

The start of the stored procedure should specify which parameters it takes:

The procedure should then try inserting the book in question, and displaying the record just added if this all goes OK:

If this fails (perhaps because the author number given doesn't exist in the authors table, so that the referential integrity constraint prevents insertion of the book), control will jump to the CATCH clause:

Running the stored procedure

Here are two calls to this stored procedure (the first will insert a row, the second will display the error details):

As the final part of this blog, I'll now take a look at raising your own errors!

If the built-in SQL errors aren't annoying enough, you could always add your own!

Two different ways to raise errors - which is better?

There are two ways to generate errors in T-SQL - using RAISERROR or THROW.  Here's a basic example of each:

As this shows, both commands do more or less the same thing.  Here are some reasons to use one or the other:

Using RAISERROR

When you call RAISERROR, the main arguments are:

1. Either a message id number, or the text of the message to be displayed.

2. The severity level of the message.

3. The state (nearly always just 1).

Here's our simple example above:

So this would raise an error of severity level 15 (still enough to end the query in question).  Alternatively, we could show a system error message:

Finally, you can create your own parametrised error message:

This would display the following message:

SQL Server fills in the values for the parameters given.

The main symbols that you can use are as follows:

The parameters have to come in the right order, and be of the right type (so in the example above, Scorpio is assigned to the first parameter, which is a string of text, and 10 to the second, which is an integer).

Using THROW

If you have SQL Server 2012, you're probably better off using THROW instead of RAISERROR.  The only arguments you can pass to this are:

1. A message id number (or a variable containing a number).

2. The message text to be displayed (often built up in a variable using FORMATMESSAGE - see below for an example).

3. The state (nearly always just 1).

Remember that THROW always creates errors with severity level 16.

Here's an example of using THROW to display a customised error message:

Here's what this would display when run:

The message the above SQL would display.

And with that, this error-handling blog is complete!  Unless you know better ... ?