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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
-- -- Create custom stored procedure -- -- Use sample database USE [AdventureWorks2012] GO -- Drop existing procedure IF OBJECT_ID ('usp_Change_Pay_Rate2', 'P') IS NOT NULL DROP PROCEDURE usp_Change_Pay_Rate2; GO -- Create new procedure CREATE PROCEDURE usp_Change_Pay_Rate2 @BusinessEntityID INT, @NewRate MONEY = 0 AS BEGIN -- Local variables DECLARE @PctChange REAL = 0; -- Get most recent record SELECT TOP 1 BusinessEntityID, RateChangeDate, Rate, PayFrequency, ModifiedDate INTO #tmp_EmployeePayHistory FROM [AdventureWorks2012].[HumanResources].[EmployeePayHistory] WHERE BusinessEntityID = @BusinessEntityID ORDER BY [ModifiedDate] DESC; -- Bad business entity id? IF (@@ROWCOUNT <> 1) BEGIN RAISERROR (65000, 16, 1, 1, N'Please make sure a valid business entity id was entered.'); RETURN; END; -- Calculate percent change SELECT @PctChange = ABS((@NewRate - [Rate]) / [Rate] * 100) FROM #tmp_EmployeePayHistory; -- Must be more than current IF (@PctChange > 30) BEGIN RAISERROR (65000, 16, 1, 2, N'Please make sure a valid business entity id was entered.'); RETURN; END; -- Update the temporary record UPDATE #tmp_EmployeePayHistory SET Rate = @NewRate, RateChangeDate = GETDATE(), ModifiedDate = GETDATE(); -- Insert into table INSERT INTO [AdventureWorks2012].[HumanResources].[EmployeePayHistory] SELECT * FROM #tmp_EmployeePayHistory; END; |
Last but not least, we need two test cases that trigger the custom errors.
|
1 2 3 4 5 6 7 8 9 |
-- -- Test custom stored procedure -- -- Bad Business Entity Id EXEC usp_Change_Pay_Rate2 -1, 50 -- Bad rate change EXEC usp_Change_Pay_Rate1 2, 500 |
The resulting output of the test calls is shown below.
|
1 2 3 4 5 6 7 8 9 10 11 |
message window: Msg 65000, Level 16, State 1, Procedure usp_Change_Pay_Rate2, Line 29 A business rule violation was detected in stored procedure [usp_Change_Pay_Rate] - 1. Please make sure a valid business entity id was entered. Msg 65000, Level 16, State 1, Procedure usp_Change_Pay_Rate2, Line 42 A business rule violation was detected in stored procedure [usp_Change_Pay_Rate] - 2. Please make sure a valid business entity id was entered. </span> |
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.
