Handling Errors and Exceptions using RAISERROR: SQL

A REISEROR statement is used to return messages to the business applications that are executing the SQL statements. This statement uses the same format as a system error or warning message generated by the database engine. Consider an example of an application that is executing a batch. If an error occurs while executing this batch, an error message will be raised and sent to the application. The application in turn will include the code to handle the error.

You can also return user-defined error messages by using the RAISERROR statement. A REISERROR severity of 1; to 19 executed in the TRY block causes the control to be transferred to the associated CATCH block.

Consider the following example of the AdventureWorks database that stores the details of the shift in which the employees work. You need to update the Shift table to update the time of a shift. While updating the shift details, you need to ensure that the difference between the start time and the end time is eight hours. If it is less than eight hours, an error is raised, and the update process is stopped.

DECLARE @Start datetime
DECLARE @End datetime
DECLARE @Date_diff int
SELECT @Start = ‘1900-01-01 23:00:00.000’, @End = ‘1900-01-02 06:00:00.000’
SELECT @Date_diff = datediff (hh, @Start, @End)
IF (@Date_diff != 8)
 RAISERROR (‘Error Raised’, 16, 1)
UPDATE HumanResources.Shift
SET StartTime = @Start, EndTime = @End
WHERE ShiftID = 3
PRINT, ‘The difference between the Start and End time should be 8 hours’

Handling Errors and Exceptions using RAISERROR: SQL Handling Errors and Exceptions using RAISERROR: SQL Reviewed by Rhett Butler on September 26, 2014 Rating: 5

No comments