RAISERROR<\/a> statement to throw an error. This is the old school way to perform this task. The THROW statement was introduced in SQL Server 2012.<\/p>\nThe 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.<\/p>\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_Rate2', 'P') IS NOT NULL \r\n DROP PROCEDURE usp_Change_Pay_Rate2;\r\nGO\r\n\r\n-- Create new procedure\r\nCREATE PROCEDURE usp_Change_Pay_Rate2 @BusinessEntityID INT, @NewRate MONEY = 0\r\nAS\r\nBEGIN\r\n\r\n -- Local variables\r\n DECLARE @PctChange REAL = 0;\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 RAISERROR (65000, 16, 1, 1, \r\n N'Please make sure a valid business entity id was entered.'); \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 RAISERROR (65000, 16, 1, 2, \r\n N'Please make sure a valid business entity id was entered.'); \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>\nLast but not least, we need two test cases that trigger the custom errors.<\/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_Rate2 -1, 50\r\n\r\n-- Bad rate change\r\nEXEC usp_Change_Pay_Rate1 2, 500\r\n\r\n<\/pre>\n<\/p>\nThe resulting output of the test calls is shown below. <\/p>\n
\r\nmessage window: \r\n\r\nMsg 65000, Level 16, State 1, Procedure usp_Change_Pay_Rate2, Line 29\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_Rate2, Line 42\r\nA business rule violation was detected in stored procedure [usp_Change_Pay_Rate] - 2. \r\nPlease make sure a valid business entity id was entered.\r\n\r\n<\/span><\/pre>\n<\/p>\nIn short, raising custom error messages inside stored procedures is a great way to add a business logic layer to the database.<\/p>\n
If you come across a RAISERROR<\/a> 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.<\/p>\n","protected":false},"excerpt":{"rendered":"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…<\/p>\n","protected":false},"author":1,"featured_media":5878,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[883,31,671,15,889,28,29],"class_list":["post-5988","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-db-dev","tag-custom-error-messages","tag-database-developer","tag-error-handling","tag-john-f-miner-iii","tag-raiserror","tag-sql-server","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/5988","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5988"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/5988\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/media\/5878"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5988"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5988"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5988"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}