How does RAISERROR work?

Intro #

Let's start with two questions:

1. What does RAISERROR(50001, 16, 1, 'dbo.GetPerson', 'PersonId') do?
2. And what do all those magic numbers mean?

To answer that, let's first look at what each parameter is doing:

                    Copy
                    
                 
RAISERROR(50001,           -- Message Id.  
          16,              -- Severity,  
          1,               -- State,  
          'dbo.GetPerson', -- 1st argument passed to msg string
          'PersonId');     -- 2nd argument passed to msg string

Note: In SQL Server, the keyword RaisError is 'mis-spelled' with only one 'e' (just like loginame) which appears to be a holdover from naming standards in Sysbase.

However, in our Global Admin DB, the custom SP we use to log errors is spelled 'correctly' as dbo.RaiseError

Message Id #

There are a couple different overloads, so as an alternative syntax, you could omit the message_id lookup altogether and pass a custom string template or a single literal like this:

                    Copy
                    
                 
RAISERROR('Hi %s', 16, 1, 'Kyle')
RAISERROR('Hi Kyle', 16, 1)

However, passing in a message ID allows you to leverage re-usable string templates in the database.

There's a special range reserved for our own error ids, according to RAISERROR docs:

Error numbers for user-defined error messages should be greater than 50000

There are tons of internal error message ids that SQL Server uses to conduct business or log errors, but we can lookup our own custom messages like this:

                    Copy
                    
                 
SELECT * FROM sys.messages s WHERE s.message_id > 50000

*This list of AHS custom error messages is also detailed in section 8.1 of our AHS SQL Environment Standards

So to answer the question for our first param, the 50001 provides the corresponding template and the final error message will combine the trailing param array and insert anywhere %s appears, forming this error message:

The stored procedure dbo.GetPerson expects the PersonId parameter, which was not supplied.

So what do the next two params do then?

Severity #

The default value is 16 which represents "general errors that can be corrected by the user."

The severity levels can be divided up into the following ranges:

* Check out the MS docs on Database Engine Error Severities for the full list, with detailed descriptions for each number

Since the message_id sets a default severity level, you can also pass -1 to use the one associated to that message. So the following two statements do the same thing:

                    Copy
                    
                 
RAISERROR(50010, 16, 1, 'My bad')
RAISERROR(50010, -1, 1, 'My bad')

Control-of-Flow #

We often use RAISERROR within a procedure to validate incoming parameters and hault execution with an informative error if they don't.

Be aware, there are specific conditions under which RAISERROR will impact the control of flow for the statement being executed:

When RAISERROR is run with a severity of 11 or higher [AND] inside a TRY block, it transfers control to the associated CATCH block.

So the following raise error statement will abort execution and transfer to the catch block

                    Copy
                    
                 
BEGIN TRY
    RAISERROR('Custom Error - Level 16', 16, 1)
    PRINT 'Happy Path Flow'
END TRY
BEGIN CATCH
    PRINT 'Catch Error Flow'
END CATCH

--- PRINTS: 'Catch Error Flow'

However, if you use a severity of < 11 or anytime you call RAISERROR outside a TRY...CATCH block, flow will proceed as normal, even if XACT_ABORT is turned on.

State & Logging Output #

The last parameter captures a number ranging from 0-127 to represent the "state" of your script. This might be helpful if you had the same error being generated multiple times in a really long script and wanted to quickly identify which line was causing the problem:

Here's a trivial example where we have two different checks for the against the same param, and we can use the 'state' param to capture which check detected the error

                    Copy
                    
                 
-- make sure the person exists
IF NOT EXISTS (SELECT 1 FROM dbo.Person p WHERE p.entity_uid = @PersonId)
    RAISERROR(50002, 16, 1, 'dbo.GetPerson', 'PersonId')

-- make sure the person has an existing record
IF NOT EXISTS (SELECT 1 FROM dbo.HearingTest h WHERE h.entity_uid = @PersonId)
    RAISERROR(50002, 16, 2, 'dbo.GetPerson', 'PersonId')

However, RAISERROR also generates metadata that is available through calling the following system functions:

• ERROR_LINE()
• ERROR_MESSAGE()
• ERROR_NUMBER()
• ERROR_PROCEDURE()
• ERROR_SEVERITY()
• ERROR_STATE()

So the state number can probably be gleaned more reliably just from relying on the line number being reported

Throw VS. RaisError #

Another way to report problems & control flow is to use the THROW command which has a similar syntax / parameters. The official guidance, according to the docs on RAISERROR is that:

New applications [SQL Server 2012+] should use THROW instead of RAISERROR

The severity is set to 16 by default, and the state value comes last, but otherwise the syntax is pretty similar:

                    Copy
                    
                 
BEGIN TRY
    --RAISERROR(50010, 16, 1, 'Whoops!')
    THROW 50010, 'Whoops!', 1
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE(),
           ERROR_NUMBER(),
           ERROR_SEVERITY(),
           ERROR_PROCEDURE(),
           ERROR_STATE(),
           ERROR_LINE()
END CATCH

One advantage is that THROW will honor the SET XACT_ABORT flag

NOTE: Currently, most of our sql templates are setup to use RAISERROR, but at some point we might investigate switching to THROW

Further Reading #

• Raise custom error message with RAISERROR in SQL Server
• What do the different RAISERROR severity levels mean?
• Why does Sql Server keep executing after raiserror when xact_abort is on?
• THROW vs. RAISERROR