Error Handling – Part 4

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 how to return a custom error message from the business logic layer to the application layer. This assumes you read the previous article on managing error messages.

In particular, I will be covering how to use the RAISERROR statement to throw an error. This is the old school way to perform this task. The THROW statement was introduced in SQL Server 2012.

The TSQL code below creates a stored procedure named [usp_Change_Pay_Rate2] which changes the pay rate for a given business entity. The same two invalid business conditions raise an error using the old school statement.

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 inside stored procedures is a great way to add a business logic layer to the database.

If you come across a RAISERROR statement in your endeavors, consider changing the code to a TRY, CATCH, and THROW pattern instead. You never know when the old way of doing things will be depreciated in SQL Server.

Related posts

Leave a Comment