• 28. 1. 2020
  • Ing. Jan Zedníček - Data Engineer & Controlling
  • 0

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.

   SELECT 1/0 -- Error
   RAISERROR ('Hi', 8, 1)

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.

Rate this post

Ing. Jan Zedníček - Data Engineer & Controlling

My name is Jan Zedníček and I have been working as a freelancer for many companies for more than 10 years. I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym.

🔥 If you found this article helpful, please share it or mention me on your website

Leave a Reply

Your email address will not be published. Required fields are marked *