SQL RAISERROR for Error Handling During Code Execution

Problem

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?

Solution

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.

RAISERROR Statement

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;
GO
EXEC sp_addmessage @msgnum = 50001,
                   @severity = 16,
                   @msgtext = 'Oh wow how many of these things do I need to add?';
GO

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

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

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.

BEGIN TRY
    SELECT 1 / 0; -- Can you guess what the error will be?
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000),
            @ErrorSeverity INT,
            @ErrorState INT;
    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;

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.

Didn’t THROW Replace RAISERROR?

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?';
GO

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

WITH LOG Option

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;

WITH NOWAIT Option

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

The next time you sit down to compile T-SQL code, ask yourself if it could benefit from error handling.
Are you interested in learning more about error handling in T-SQL? Joydip Kanjilal has an excellent article looking at every aspect: Error Handling in SQL Server with Try…Catch.
Aaron Bertrand has an incredible write-up on the performance impacts of different error handling techniques: Performance impact of different error handling techniques.

Resources

Community

Subscribe