Why Do We Still Use RAISERROR?

I don’t use RAISERROR often – I usually forget which severity code to use. After looking at a sprinkling of them recently I decided it was time for a refresher, so come along for the ride.

If you check out the online documentation it states that “New applications should use THROW instead”. It also sounds like its used to raise ‘RROR’s (whatever they are?). Neither are quite the whole story though. Let’s get into it.

RAISERROR is used to communicate conditions within a script. It can be used as one approach for debugging, it can relay information or warnings, or it could be used to raise exceptions – we’ll come back to those.

A simple implementation may look like this:

RAISERROR ('Staging table is empty, nothing to load', 10, 1);

The parameters used here are:

• The Message contains the information to be relayed. You can also use a variable (with arguments if needed), or a msg_id from sys.messages if you prefer
• Severity indicates how the message should be treated, in this case using 10 to indicate an informational message. A full list can be found in the documentation
• State isn’t functional but can be changed if the same message is used in multiple locations to help identify the specific instance which has raised the message

The example above will result in the message below if ran in SSMS:

These messages can also be useful for Agent jobs, since they’re visible in the logs (msdb.dbo.sysjobhistory) and log viewer:

There are additional options which can be added on, two of the more useful:

RAISERROR ('Staging table is empty, nothing to load', 10, 1)
    WITH NOWAIT, LOG;

• NOWAIT returns the message to the client immediately, useful for long running scripts where you want to track progress
• LOG will record the details into the SQL Server application and error logs as well as Event Viewer. This can be helpful for monitoring tools which aren’t specifically integrated with SQL Server

Severities above 10 are treated as errors. Anything 19 and over will require elevated permissions and may need the WITH LOG option, and 20 or above will terminate the connection. They also appear more prevalent in SSMS:

But here we need to take a step back to the start, where the documentation noted that the THROW statement should supersede the RAISERROR statement for new code. We shouldn’t use RAISERROR to raise errors (ironically) in new developments.

THROW was introduced back in SQL Server 2012 (which makes me feel old) as a method to raise exceptions in a familiar .NET style. When combined with a TRY…CATCH block, you have powerful tools for error handling and program flow. For example:

BEGIN TRY
	SELECT 1 / 0;
END TRY
BEGIN CATCH
	THROW 50000, 'Does not math', 1;
END CATCH

The syntax is similar to the RAISERROR statement:

• An Error Number is required here and must be 50000 or greater. Similar to the optional message Id used by RAISERROR, although unrelated to sys.messages
• Message contains details the exception being thrown to be passed back to the caller
• State again provides the ability to use a unique reference where there are multiple instances of the same exception

Note that there’s no Severity parameter here. All exceptions raised with THROW use severity 16. Also, all parameters can be provided as variables.

At first glance, THROW looks like RAISERROR with a different name. I don’t want to dig too deep here, but it’s more powerful. For example in the code above, the CATCH block could have simply contained the line THROW; (no parameters needed!) which would have taken the original exception (divide by zero) and simply pass that exception back to the calling application.

So in summary:

• For debugging or informational messages, stick with RAISERROR using appropriate parameters
• If you’re handling errors, use THROW in combination with well-structured TRY…CATCH blocks

Oh and I’m sure you’re wondering (as was I) – it’s spelt RAISERROR as a holdover from SQL’s Sybase roots. It may be the same person who created sp_helprotect 🤷‍♂️