T-SQL Tutorial

RAISERROR statement


How to use RAISERROR statement?

RAISERROR is a T-SQL statement in SQL Server that allows users to generate an error message and return it to the calling application or batch. The RAISERROR statement can be used to raise both system-defined and user-defined errors, and can also be used to generate ad-hoc error messages.

The basic syntax of the RAISERROR statement is as follows:


RAISERROR ( {error_number} , {severity} , {state} , {message} [ , {argument1} , ... ] )


Where:
error_number: A user-defined error number.
severity: A value between 0 and 25 that represents the severity of the error.
state: An optional value that can be used to indicate the error state.
message: The error message text.
argument1: Additional arguments that can be included in the error message.


The RAISERROR statement can be used in a number of ways, such as to signal a problem with data integrity or to return an error message to the client application. It can also be used in conjunction with TRY-CATCH blocks to handle errors in a more controlled manner. It's important to note that RAISERROR statement can be used to raise an error with a state of 1 to 127. but it is reserved for system use in state of 128 and above.





RAISERROR example

The following example shows how to handle exceptions in SQL Server using RAISERROR statement.

BEGIN TRY
RAISERROR ('Error in TRY block.', 16, 1);
END TRY
BEGIN CATCH
DECLARE @@ErrMessage VARCHAR(4000);
DECLARE @@ErrSeverity INT;
DECLARE @@ErrState INT;
SELECT
@@ErrMessage = ERROR_MESSAGE(),
@@ErrSeverity = ERROR_SEVERITY(),
@@ErrState = ERROR_STATE();
RAISERROR (@@ErrMessage, @@@ErrState, @@ErrState);
END CATCH;