The usage of RAISERROR in SQL Server is described in detail.

The usage of RAISERROR in SQL Server is described in detail.

Use of RAISERROR in SQL Server

Raiserror is used to throw an error. [The following information is from the help of SQL server 2005]

The syntax is as follows:

RAISERROR ( { msg_id | msg_str | @local_variable }          { ,severity ,state }          [ ,argument [ ,...n ] ]      )      [ WITH option [ ,...n ] ]

Brief description:

First parameter: {msg_id | msg_str | @ local_variable}

Msg_id: indicates the message code defined in the sys. messages table;
Use sp_addmessage to store the User-Defined error message number in the sys. messages directory view.
The error code of the custom error message must be greater than 50000.

Msg_str: indicates a user-defined message. The error message can contain a maximum of 2047 characters;
(If it is a constant, use n'xxx' because it is nvarchar)
When msg_str is specified, RAISERROR will trigger an error message with the error code 5000.

@ Local_variable: format the string variable in msg_str mode.

Second parameter: severity

The severity level of the message associated with the user-defined message. (This is important)
Any user can specify a severity level between 0 and 18.
Within the closed interval of [], catch is not skipped;
If it is [11,19], it will jump to catch;
If [20, infinite), the database connection is terminated directly;

Third parameter: state

If the same user-defined error is thrown at multiple locations,
You can use a unique status number for each location to locate the code segment that causes the error.

Any integer between 1 and 127. (The default value of state is 1)
An error is generated when the state value is 0 or greater than 127!

Fourth parameter: argument

The parameter used to replace msg_str or the variable defined in the message corresponding to msg_id.

Fifth parameter: option

Incorrect custom options can be any of the following values:
LOG: records errors in error logs and application logs;
NOWAIT: send the message to the client immediately;
SETERROR: set the value of @ ERROR and ERROR_NUMBER to msg_id or 50000;

[SQL] sample code

-- Example 1

DECLARE @raiseErrorCode nvarchar(50)SET @raiseErrorCode = CONVERT(nvarchar(50), YOUR UNIQUEIDENTIFIER KEY)RAISERROR('%s INVALID ID. There is no record in table',16,1, @raiseErrorCode) 

-- Example 2

RAISERROR (       N'This is message %s %d.', -- Message text,       10,            -- Severity,       1,             -- State,       N'number',         -- First argument.       5             -- Second argument.     ); -- The message text returned is: This is message number 5.GO

-- Example 3

RAISERROR (N'<<%*.*s>>', -- Message text.      10,      -- Severity,      1,      -- State,      7,      -- First argument used for width.      3,      -- Second argument used for precision.      N'abcde');  -- Third argument supplies the string.-- The message text returned is: <<  abc>>.GO

-- Example 4

RAISERROR (N'<<%7.3s>>', -- Message text.      10,      -- Severity,      1,      -- State,      N'abcde');  -- First argument supplies the string.-- The message text returned is: <<  abc>>.GO

-- Example 5

-- A. return an error message from the CATCH Block

The following code example shows how to use RAISERROR in a TRY block to redirect execution to the associated CATCH block.
It also shows how to use RAISERROR to return information about errors that call CATCH blocks.

BEGIN TRY  RAISERROR ('Error raised in TRY block.', -- Message text.        16, -- Severity.        1 -- State.        );END TRYBEGIN CATCH  DECLARE @ErrorMessage NVARCHAR(4000);  DECLARE @ErrorSeverity INT;  DECLARE @ErrorState INT;  SELECT     @ErrorMessage = ERROR_MESSAGE(),    @ErrorSeverity = ERROR_SEVERITY(),    @ErrorState = ERROR_STATE();  RAISERROR (@ErrorMessage, -- Message text.        @ErrorSeverity, -- Severity.        @ErrorState   -- State.        );END CATCH;

-- Example 6

-- B. Create an ad hoc message in sys. messages
The following example shows how to trigger messages stored in the sys. messages directory view.
The message is stored in the sp_addmessage system and added to the sys. messages directory view with message number 50005.

sp_addmessage @msgnum = 50005,        @severity = 10,        @msgtext = N'<<%7.3s>>';GORAISERROR (50005, -- Message id.      10,  -- Severity,      1,   -- State,      N'abcde'); -- First argument supplies the string.-- The message text returned is: <<  abc>>.GOsp_dropmessage @msgnum = 50005;GO

-- Example 7

-- C. Use local variables to provide message text
The following code example shows how to use local variables to provide message text for the RAISERROR statement.

sp_addmessage @msgnum = 50005,       @severity = 10,       @msgtext = N'<<%7.3s>>';GORAISERROR (50005, -- Message id.      10,  -- Severity,      1,   -- State,      N'abcde'); -- First argument supplies the string.-- The message text returned is: <<  abc>>.GOsp_dropmessage @msgnum = 50005;GO

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!