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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- -- Create custom error messages -- -- Use master database USE [master] GO -- Remove old message IF EXISTS (SELECT error FROM sys.sysmessages WHERE error = 65000) EXEC sys.sp_dropmessage 65000 GO -- Add new message (custom) EXEC sys.sp_addmessage @msgnum = 65000 , @severity = 16 , @msgtext = N'A business rule violation was detected in stored procedure [usp_Change_Pay_Rate] - %d. %s' , @lang = 'us_english'; GO |
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.
|
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 71 |
-- -- Create custom stored procedure -- -- Use sample database USE [AdventureWorks2012] GO -- Drop existing procedure IF OBJECT_ID ('usp_Change_Pay_Rate1', 'P') IS NOT NULL DROP PROCEDURE usp_Change_Pay_Rate1; GO -- Create new procedure CREATE PROCEDURE usp_Change_Pay_Rate1 @BusinessEntityID INT, @NewRate MONEY = 0 AS BEGIN -- Local variables DECLARE @PctChange REAL = 0; DECLARE @ErrorMsg NVARCHAR(2048) = ''; -- 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 SET @ErrorMsg = FORMATMESSAGE(65000, 1, N'Please make sure a valid business entity id was entered.'); THROW 65000, @ErrorMsg, 1; RETURN; END; -- Calculate percent change SELECT @PctChange = ABS((@NewRate - [Rate]) / [Rate] * 100) FROM #tmp_EmployeePayHistory; -- Must be more than current IF (@PctChange > 30) BEGIN SET @ErrorMsg = FORMATMESSAGE(65000, 2, N'Please make sure the pay rate change is less than 30%.'); THROW 65000, @ErrorMsg, 1; 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_Rate1 -1, 50 -- Bad rate change EXEC usp_Change_Pay_Rate1 1, 500 |
The resulting output of the test calls is shown below.
|
1 2 3 4 5 6 7 8 9 |
message window: Msg 65000, Level 16, State 1, Procedure usp_Change_Pay_Rate1, Line 31 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_Rate1, Line 44 A business rule violation was detected in stored procedure [usp_Change_Pay_Rate] - 2. Please make sure the pay rate change is less than 30. |
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.
