This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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! Error-handling and error messages The parts of an error A practical example of error-handling 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.

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!

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

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

Using RAISERROR

When you call RAISERROR, the main arguments are:

Either a message id number, or the text of the message to be displayed.
The severity level of the message.
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:

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

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

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

Parts of this blog
Error-handling and error messages The parts of an error A practical example of error-handling Raising Errors (this blog)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC