SQL RAISERROR – How to Call an Error and Abort the Script

In certain situations, it’s necessary to call an error within an SQL script and interrupt its execution. Typical scenarios include failing a data quality check or the need to invoke an error within a BEGIN /END TRY BEGIN/END CATCH construct. In such cases, you can use the RAISERROR command or the newer THROW command. In this article, we’ll focus on the former, RAISERROR.

RAISERROR Syntax in SQL Server

The syntax is simple, and the command contains three arguments:

RAISERROR (msg, severity, state)

• msg (message): The error message.
• severity: The error severity, which can range from 0 to 25. Severity <17 will not interrupt the script, as we’ll see later.
• state: An optional parameter ranging from 0 to 255.

Example: Information, User Error, and Severity >17

1. Severity 0 – 10: Information
2. Severity 11 – 16: User error
3. Severity 17 – 25: Various other error types. Only these severities will interrupt the script.

We’ll demonstrate this with a script that we run three times, using severity levels 8, 16, and 17. First, we cause an error (division by zero) within the TRY block, and then we call RAISERROR with different severities within the CATCH block.

BEGIN TRY
   SELECT 1/0 -- Error
END TRY
BEGIN CATCH
   RAISERROR ('Hi', 8, 1)
END CATCH

Severity 8: This can be a bit confusing. We call RAISERROR, but we only see an informational message (the text is not in red), and the script continues with “Hop, Hop, Hop.”

Severity 16: This is even more confusing. We can see an error and red text in the output, but the script still proceeds with “Hop, Hop, Hop.”

Severity 17: Only from severity 17 to 25 will the script be interrupted, so be cautious.

You can find more information in the Microsoft documentation. The advantages of THROW compared to RAISERROR are discussed in this blog post by Pinal Dave: Convert Old Syntax of RAISERROR to THROW.