WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 519 reviews for our classroom and online training
How error messages work in SQL (including TRY, CATCH, RAISERROR and THROW)
Part four of a four-part series of blogs

You can use BEGIN TRY to trap errors from SQL Server 2005 onwards, and also raise your own errors using RAISERROR or THROW - this blog explains all!

  1. Error-handling and error messages
  2. The parts of an error
  3. A practical example of error-handling
  4. Raising Errors (this blog)

If you want to learn more about SQL in a classroom setting, you could consider attending a Wise Owl course; otherwise, see the rest of this comprehensive SQL tutorial.

Posted by Andy Brown on 03 May 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Raising Errors

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

One reason to do this might be to signal to a client application that a process hadn't completed successfully.

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:

Reason Notes
Compatibility THROW only works from SQL Server 2012 (so if you're using 2008 R2 or earlier, ignore this table!).
Future-proofing It seems likely from reading websites that Microsoft will eventually deprecate RAISERROR.
Ease of syntax You must end the line before a THROW statement with a semi-colon (as for the above example).
Custom error numbers RAISERROR can only raise error messages already in the sys.messages table (although you can add your own messages, as explained below).  With THROW you can use any error number between 50000 and 2147483647!
Levels of severity THROW automatically generates errors of severity level 16 (with RAISERROR you can choose your severity level).
Formatting messages You can format messages more with RAISERROR (although why you'd want to do this within SQL I can't imagine).


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"


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:

Star sign message

SQL Server fills in the values for the parameters given.


The main symbols that you can use are as follows:

Symbol What it means
%d or %i Any integer
%s 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!).


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:

-- add a message to the system messages table

EXEC sys.sp_addmessage


@severity = 16,

@msgtext = N'No %s or %s can run queries after %i hours.'



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

The message for star signs

The message the above SQL would display.

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

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

This blog has 0 threads Add post