I am going to continue my articles on controlling the execution flow of statements in a Transaction SQL (T-SQL) program. <\/p>\n
Every program has to be deterministic on what step to execute next. Otherwise, the computer would be clueless on what to do next.<\/p>\n
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.<\/p>\n
Today, I want to talk about creating, altering and deleting custom error messages. Therefore, the sys.sp_addmessage<\/a>, sys.sp_altermessage<\/a> and sys.sp_dropmessage<\/a> are key functions you should be familiar with for maintaining these messages.<\/p>\n Why define a custom error message?<\/span><\/p>\n 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.<\/p>\n How do we use the custom error message?<\/span><\/p>\n I will be going over a code example that uses the THROW<\/a> statement to raise an error. This is the new school way of throwing an error.<\/p>\n 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.<\/p>\n 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).<\/p>\n In mathematics, the percent change<\/a> 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.<\/p>\n The FORMATMESSAGE<\/a> 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<\/a> statement exits the stored procedure after the invalid condition is detected.<\/p>\n Last but not least, we need two test cases that trigger the custom errors.<\/p>\n The resulting output of the test calls is shown below. <\/p>\n 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.<\/p>\n\r\n--\r\n-- Create custom error messages\r\n--\r\n\r\n-- Use master database\r\nUSE [master]\r\nGO\r\n\r\n-- Remove old message\r\nIF EXISTS (SELECT error FROM sys.sysmessages WHERE error = 65000)\r\n EXEC sys.sp_dropmessage 65000\r\nGO\r\n\r\n-- Add new message (custom)\r\nEXEC sys.sp_addmessage \r\n @msgnum = 65000\r\n , @severity = 16\r\n , @msgtext = \r\n N'A business rule violation was detected in stored procedure [usp_Change_Pay_Rate] - %d. %s'\r\n , @lang = 'us_english'; \r\nGO\r\n\r\n<\/pre>\n<\/p>\n
\r\n\r\n--\r\n-- Create custom stored procedure\r\n--\r\n\r\n-- Use sample database\r\nUSE [AdventureWorks2012]\r\nGO\r\n\r\n-- Drop existing procedure\r\nIF OBJECT_ID ('usp_Change_Pay_Rate1', 'P') IS NOT NULL \r\n DROP PROCEDURE usp_Change_Pay_Rate1;\r\nGO\r\n\r\n-- Create new procedure\r\nCREATE PROCEDURE usp_Change_Pay_Rate1 @BusinessEntityID INT, @NewRate MONEY = 0\r\nAS\r\nBEGIN\r\n\r\n -- Local variables\r\n DECLARE @PctChange REAL = 0;\r\n DECLARE @ErrorMsg NVARCHAR(2048) = '';\r\n\r\n -- Get most recent record\r\n SELECT TOP 1\r\n BusinessEntityID,\r\n RateChangeDate,\r\n Rate,\r\n PayFrequency,\r\n ModifiedDate\r\n INTO \r\n #tmp_EmployeePayHistory\r\n FROM \r\n [AdventureWorks2012].[HumanResources].[EmployeePayHistory]\r\n WHERE\r\n BusinessEntityID = @BusinessEntityID \r\n ORDER BY\r\n [ModifiedDate] DESC;\r\n\r\n -- Bad business entity id?\r\n IF (@@ROWCOUNT <> 1)\r\n BEGIN\r\n SET @ErrorMsg = FORMATMESSAGE(65000, 1, N'Please make sure a valid business entity id was entered.'); \r\n THROW 65000, @ErrorMsg, 1; \r\n RETURN;\r\n END;\r\n \r\n -- Calculate percent change \r\n SELECT @PctChange = ABS((@NewRate - [Rate]) \/ [Rate] * 100)\r\n FROM #tmp_EmployeePayHistory;\r\n\r\n -- Must be more than current\r\n IF (@PctChange > 30)\r\n BEGIN\r\n SET @ErrorMsg = FORMATMESSAGE(65000, 2, N'Please make sure the pay rate change is less than 30%.'); \r\n THROW 65000, @ErrorMsg, 1; \r\n RETURN;\r\n END;\r\n\r\n -- Update the temporary record\r\n UPDATE \r\n #tmp_EmployeePayHistory\r\n SET \r\n Rate = @NewRate, \r\n RateChangeDate = GETDATE(), \r\n ModifiedDate = GETDATE();\r\n\r\n -- Insert into table\r\n INSERT INTO [AdventureWorks2012].[HumanResources].[EmployeePayHistory]\r\n SELECT * FROM #tmp_EmployeePayHistory;\r\n\r\nEND;\r\n<\/pre>\n<\/p>\n\r\n--\r\n-- Test custom stored procedure\r\n--\r\n\r\n-- Bad Business Entity Id\r\nEXEC usp_Change_Pay_Rate1 -1, 50\r\n\r\n-- Bad rate change\r\nEXEC usp_Change_Pay_Rate1 1, 500\r\n\r\n<\/pre>\n<\/p>\n
\r\nmessage window: \r\n\r\nMsg 65000, Level 16, State 1, Procedure usp_Change_Pay_Rate1, Line 31\r\nA business rule violation was detected in stored procedure [usp_Change_Pay_Rate] - 1. \r\nPlease make sure a valid business entity id was entered.\r\n\r\nMsg 65000, Level 16, State 1, Procedure usp_Change_Pay_Rate1, Line 44\r\nA business rule violation was detected in stored procedure [usp_Change_Pay_Rate] - 2. \r\nPlease make sure the pay rate change is less than 30.\r\n\r\n<\/pre>\n<\/p>\n