SQL RAISERROR to Better Handle Errors

By:   |   Updated: 2022-11-03   |   Comments (1)   |   Related: > Error Handling


In a previous tip, I outlined the benefits of using THROW for raising an exception in T-SQL. I briefly mentioned the older method of RAISERROR. Even though Microsoft recommends using THROW for any new development, I thought it fair to look at RAISERROR. Does RAISERROR provide benefits post 2008R2 version of SQL Server? Also, what can RAISERROR do that THROW cannot?


In this tip, I'll explore RAISERROR in detail. We'll start by looking at what it is and when you would use it. Continuing, I'll highlight a few of the benefits RAISERROR offers. Finally, I'll offer a few practical takeaways to be confident in your choice of exception-raising methods.


You can't talk about RAISERROR without first mentioning the missing vowel. Likely someone at Microsoft knows why they chose RAISERROR versus RAISEERROR. The story might even reside somewhere on the internet. I'll admit I haven't spent much time looking for answers. For the longest time, I called RAISERROR a command until someone corrected me while presenting.

I started using RAISERROR in SQL Server 2005. Online references say SQL Server introduced it in version 7.0.

In short, RAISERROR allows a developer to generate an exception in SQL Server. Microsoft's website describes RAISERROR as a statement that generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. You'll find an example of the syntax below.

RAISERROR(50001, 16, 1, 'Oh no! Something bad just happened!');

You pass in a message id, message text, severity, and whatever state you want. I don't recall when I used something other than one for the state. If you don't pass in a message Id, SQL substitutes 50000 as the default. The statement fails if you neglect to pass in severity or state.

If you run the syntax above, it fails. The message Id of 50001 must exist in the sys.messages catalog view. You can add custom messages to the sys.messages catalog view with the syntax below.

USE master;
EXEC sp_addmessage @msgnum = 50001,
                   @severity = 16,
                   @msgtext = 'Oh wow how many of these things do I need to add?';

Now, if we rerun the command, SQL returns the desired error.

I dislike adding custom messages. I find them hard to maintain. So, I rarely use a message Id. The syntax below executes without an issue.

RAISERROR('Oh no! Something bad just happened!', 16, 1);
Error Message

A valuable feature of the message text includes building messages dynamically.

For example, if you want to supply a piece of information back to the end user or log it. See the code block below for an example. I've implemented this in the past with parameters passed into a stored procedure.

DECLARE @InputParameter1 INT = 7;
DECLARE @InputParameter2 INT = 42;
DECLARE @msgtext VARCHAR(100);

SET @msgtext = CONCAT('The ultimate answer to life is not ',@InputParameter1,' it is ',@InputParameter2);

RAISERROR (@msgtext,16,1);
Error Message

With exception handling, I place RAISERROR within a TRY…CATCH block. TRY…CATCH allows you to handle error messages like other programming languages, such as C#. Examples of TRY…CATCH and RAISERROR are below.

    SELECT 1 / 0; -- Can you guess what the error will be?
    DECLARE @ErrorMessage NVARCHAR(4000),
            @ErrorSeverity INT,
            @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);

Did you notice I omitted the error number above? Unfortunately, RAISERROR will fail if I try and pass it in. Several examples online skip the error number as well.


On their website, Microsoft suggests using THROW for any new application development. If you want an easy way to raise and handle exceptions, THROW wins hands down. However, RAISERROR does a few tricks that THROW does not. I'll outline just a few of them below—primarily ones I've used in practice.

Informational Messages

If you execute THROW outside of a CATCH block, SQL uses a severity of 16. A severity level of 16 in conjunction with THROW terminates your batch. With RAISERROR, you can pass in lower severity levels, for instance, zero or one. Even if you use 16, subsequent statements execute.

RAISERROR('This is not an important message.', 16, 1);
PRINT 'Will this even print?';
Informational Messages

From the screenshot above, you can see the statement after RAISERROR executes. THROW always terminates the batch. SQL would have neglected the PRINT statement.


You can use the WITH LOG option if you need to log an informational message to the SQL Server error log. Since THROW uses a severity of 16, this option isn't on the table. When I log something, it's usually to a table and not the error log, so this one isn't that big of a deal for me, but it's something I've used in the past.

RAISERROR('I feel the need to add this to the error log', 0, 1) WITH LOG;
SQL Server Error Log


You can specify the WITH NOWAIT option if you need to have a quick message returned to the client. An example might be a progress indicator. You commonly see this when employing a WHILE loop or running several statements in a row.

PRINT 'Please stay calm and carry on';
PRINT 'This print statement is way too long; I mean what are we even trying to say here'
WAITFOR DELAY '00:00:10'
PRINT 'The final countdown'

When you run the statement above, SQL doesn't return the first two prints until after the WAITFOR completes. On the other hand, here is an example with RAISERROR. Make sure you navigate to the messages tab.

RAISERROR('Please stay calm and carry on',0,1) WITH NOWAIT;
RAISERROR('This print statement is way too long, I mean what are we even trying to say here',0,1) WITH NOWAIT;
WAITFOR DELAY '00:00:10'
RAISERROR('The final countdown',0,1) WITH NOWAIT;

Notice SQL returns the first two messages before the WAITFOR completes. This single ability is why I still apply RAISERROR.

Older Version

Finally, if for some reason, you still thrive on SQL 2008R2 or prior, THROW isn't an option. I highly recommend you research upgrading if this is the case. I completely understand if a legacy application holds you back.

Bottom Line

I alluded to it initially, but for typical exception handling, I'd pick THROW unless you need one of the features listed above. For me, the ability to return immediate informational messages wins as the most significant selling point. In summary, RAISERROR still delivers as a fantastic tool in your T-SQL toolbox. Please comment below on why you've used RAISERROR in the past and continue using it today.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jared Westover Jared Westover is a passionate technology specialist at Crowe, helping to build data solutions. For the past two decades, he has focused on SQL Server, Azure, Power BI, and Oracle. Besides writing for MSSQLTips.com, he has published 12 Pluralsight courses about SQL Server and Reporting Services. Jared enjoys reading and spending time with his wife and three sons when he's not trying to make queries go faster.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-11-03

Comments For This Article

Thursday, June 1, 2023 - 1:54:17 PM - JP Back To Top (91244)
Great. But do you know how to exploit this error message to the calling PS script so that it exit with RC 1 to the scheduler

get free sql tips
agree to terms