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.
BEGIN TRY
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)
ELSE
BEGIN
UPDATE HumanResources.Shift
SET StartTime = @Start, EndTime = @End
WHERE ShiftID = 3
END
END TRY
BEGIN CATCH
PRINT, ‘The difference between the Start and End time should be 8 hours’
END CATCH
GO
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.
BEGIN TRY
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)
ELSE
BEGIN
UPDATE HumanResources.Shift
SET StartTime = @Start, EndTime = @End
WHERE ShiftID = 3
END
END TRY
BEGIN CATCH
PRINT, ‘The difference between the Start and End time should be 8 hours’
END CATCH
GO