Error messages in SQL (including TRY, CATCH, RAISERROR and THROW)


To totally unlock this section you need to Log-in


Login

You can use BEGIN TRY to trap errors from SQL Server 2005 onwards, and also raise your own errors using RAISERROR or THROW.

The parts of an error

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:

Error messages in SQL (including TRY, CATCH, RAISERROR and THROW)

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

Error messages in SQL (including TRY, CATCH, RAISERROR and THROW)

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

-- a stored procedure which will fail

ALTER PROC spTest
AS
-- create an integer variable
DECLARE @i int
BEGIN TRY
-- try to store text in it
SET @i='owl'
END TRY
BEGIN CATCH
-- display details of error
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage
END CATCH

This would produce the following output:

Error messages in SQL (including TRY, CATCH, RAISERROR and THROW)

Built-in error messages

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

-- display all built-in errors

SELECT * FROM sys.messages

On our example instance of SQL Server 2008 R2 this gives 98,318 error messages, while on SQL Server 2012 it gives 230,186.

Here are the first few error messages for 2008 R2:

Error messages in SQL (including TRY, CATCH, RAISERROR and THROW)

NOTE: Error message enthusiasts might like to use the system stored procedure sp_AddMessage to add their own messages to those stored in the Sys.Messages table.

The Error Number

On our 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:

Error messages in SQL (including TRY, CATCH, RAISERROR and THROW)

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.

The Error Severity

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

Error messages in SQL (including TRY, CATCH, RAISERROR and THROW)

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.

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:

CREATE PROC spSub(@num int) AS

-- divide 1 by 0
SELECT @num / 0

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

CREATE PROC spMain AS

BEGIN TRY
-- try to execute the subprocedure
EXEC spSub 1
END TRY
BEGIN CATCH
-- display details of error
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage
END CATCH

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

Error messages in SQL (including TRY, CATCH, RAISERROR and THROW)

The Error Line Number

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

Error messages in SQL (including TRY, CATCH, RAISERROR and THROW)

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

Error messages in SQL (including TRY, CATCH, RAISERROR and THROW)

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.

A practical example of error-handling

Let's begin filling some data on two tables on the example database:

Error messages in SQL (including TRY, CATCH, RAISERROR and THROW)

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:

-- author number 3 does exist, so this should work

spAddBook 'The L-Shaped Room', 3

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

-- there is no author number 4

spAddBook 'The Pelican Brief', 4

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

Error messages in SQL (including TRY, CATCH, RAISERROR and THROW)

Building the stored procedure

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

-- procedure to insert a book

ALTER PROC spAddBook(
@BookName varchar(100),
@AuthorId int
)
AS

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

BEGIN TRY

-- try inserting book
INSERT INTO tblBook(
BookName,
AuthorId
) VALUES (
@BookName,
@AuthorId
)
-- show last inserted book if worked
SELECT TOP 1 * FROM tblBook
ORDER BY BookId DESC
END TRY

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:

BEGIN CATCH

-- if couldn't insert book, show why
SELECT
'Could not do' AS ErrorMessage,
ERROR_NUMBER() AS 'Error no',
'Author ' + CAST(@AuthorId AS varchar(10)) + ' not found' AS Problem
END CATCH

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):

-- add a book by Lynne Reid Banks

spAddBook 'The L-Shaped Room', 3
-- there is no author 5 yet!
spAddBook 'The Lacuna', 5

Raising 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:

-- raise error of severity level 15

RAISERROR('This went pear-shaped',15,1);
-- alternatively, throw the same error message
THROW 50000, 'This went pear-shaped', 1

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

Error messages in SQL (including TRY, CATCH, RAISERROR and THROW)

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:

-- raise error of severity level 15

RAISERROR('This went pear-shaped',15,1);

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:

-- Show message: "the article resolver supplied

-- is either invalid or nonexistent"
RAISERROR(20020,12,1)

Finally, you can create your own parametrised error message:

-- show a message using parameters

RAISERROR('Star sign %s limited to %i queries per day',
16, 1, 'Scorpio', 10)

This would display the following message:

Error messages in SQL (including TRY, CATCH, RAISERROR and THROW)

The main symbols that you can use are as follows:

  • %d or %i: which means Any integer
  • %s: which means Any string of text

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).

There's quite a bit more you can do with formatting parameters within error messages - if you know the printf statement in C you'll be able to guess what's possible (otherwise, you're not missing anything, believe me!).

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:

  • A message id number (or a variable containing a number).
  • The message text to be displayed (often built up in a variable using FORMATMESSAGE - see below for an example).
  • 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:

-- add a message to the system messages table

EXEC sys.sp_addmessage
@msgnum=60001,
@severity = 16,
@msgtext = N'No %s or %s can run queries after %i hours.'
GO
-- create a variable to hold message text
DECLARE @WolErrorMessage nvarchar(2048) = FORMATMESSAGE(60001,
'Scorpio','Aquarius', 17);
-- previous line must end in semi-colon
THROW 60000, @WolErrorMessage, 1;

Here's what this would display when run:

Error messages in SQL (including TRY, CATCH, RAISERROR and THROW)

You can also use sys.sp_addmessage to add messages for display using RAISERROR, in a similar way.