Error Handling – Part 3

I am going to continue my articles on controlling the execution flow of statements in a Transaction SQL (T-SQL) program.

Every program has to be deterministic on what step to execute next. Otherwise, the computer would be clueless on what to do next.

The only exception to this rule is error handling. A runtime error may cause the program to branch to a error handler and exit the program.

Today, I want to talk about creating, altering and deleting custom error messages. Therefore, the sys.sp_addmessage, sys.sp_altermessage and sys.sp_dropmessage are key functions you should be familiar with for maintaining these messages.

Why define a custom error message?

These messages are great for implementing the business logic with stored procedures in the database. When invalid condition arises during a requested task, return the custom error message to the application.

How do we use the custom error message?

I will be going over a code example that uses the THROW statement to raise an error. This is the new school way of throwing an error.

The code snippet below creates a custom error message for error number 65000. This is in preparation for a new custom stored procedure in the Adventure Works database.

The TSQL code below creates a stored procedure named [usp_Change_Pay_Rate1] which changes the pay rate for a given business entity. There are two custom error calls using the THROW statement. One for invalid business entity (1) and one for unusual percent change in rate (2).

In mathematics, the percent change is defined as how much the new value changes from the old value. We want to generate an error when the increase/decrease in pay rate exceeds 30 percent.

The FORMATMESSAGE function is used to take our two parameters (section of procedure and detailed message) and package them up for a call to the THROW statement. The RETURN statement exits the stored procedure after the invalid condition is detected.

Last but not least, we need two test cases that trigger the custom errors.

The resulting output of the test calls is shown below.

In short, raising custom error messages with the THROW statement inside stored procedures is a great way to add a business logic layer to the database.

Next time I will be talking about how you can raise a user defined error using the RAISERROR statement.

Related posts

Leave a Comment