The Usage of RAISERROR in SQL Server

Time:2019-4-20

Use of RAISERROR in SQL Server

The role of raiserror: raiserror is used to throw an error。 [The following information comes from the help of SQL Server 2005]

Its grammar is as follows:


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

Brief description:

The first parameter: {msg_id | msg_str | @local_variable}

Msg_id: Represents a message code that can be defined in a sys. messages table;
User-defined error message number stored in sys.messages catalog view using sp_addmessage.
The error number of user-defined error messages should be greater than 50,000.

Msg_str: Representation can also be a user-defined message with a maximum of 2047 characters.
(If it’s a constant, use N’xxxx’, because it’s nvarchar)
When msg_str is specified, RAISERROR raises an error message with an error number of 5000.

@ Local_variable: Represents or formats string variables as msg_str. _

Second parameter: severity

User-defined severity level associated with the message. (This is very important)
Any user can specify a severity level between 0 and 18.
In the closed interval of [0,10], catch will not be jumped.
If [11,19], jump to catch.
If [20, infinity], terminate the database connection directly;

The third parameter: state

If the same user-defined error is triggered in multiple locations,
Using a unique status number for each location helps to find the code segment that caused the error.
         
Any integer between 1 and 127. (State defaults to 1)
Errors are generated when the state value is 0 or greater than 127!

The fourth parameter: argument

The parameters used to replace the variables defined in msg_str or messages corresponding to msg_id.

Fifth parameter: option

The wrong custom option can be any of the values in the following table:
LOG: Record errors in error logs and application logs;
NOWAIT: Send the message to the client immediately.
SETERROR: Set the @@ERROR value and ERROR_NUMBER value to msg_id or 50000;

[SQL] code example

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. Returns an error message from the CATCH block

The following code example shows how to use RAISERROR in TRY block to jump execution into the associated CATCH block.
It also shows how to use RAISERROR to return information about errors in calling CATCH blocks.



BEGIN TRY
  RAISERROR ('Error raised in TRY block.', -- Message text.
        16, -- Severity.
        1 -- State.
        );
END TRY
BEGIN 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

Create impromptu messages in sys. messages
The following example shows how to trigger messages stored in the sys. messages directory view.
The message is added to the sys. messages catalog view with message number 50005 through the sp_addmessage system stored procedure.



sp_addmessage @msgnum = 50005,
        @severity = 10,
        @msgtext = N'<<%7.3s>>';
GO

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

sp_dropmessage @msgnum = 50005;
GO

— Example 7

– C. Providing message text with local variables
The following code example shows how to use local variables to provide message text for RAISERROR statements.



sp_addmessage @msgnum = 50005,
        @severity = 10,
        @msgtext = N'<<%7.3s>>';
GO

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

sp_dropmessage @msgnum = 50005;
GO

Thank you for reading, I hope to help you, thank you for your support on this site!

Recommended Today

Perl open local / server image

Reprinted in my blog Gardenhttps://www.cnblogs.com/kenso… index.html <html> <body> <h2> perl read img </h2> <img src = “display.html”> </body> </html> display.html It can be rewritten as display.cgi display.html #! /usr/bin/perl #Define picture and picture type (can be changed into variable according to requirement) $image = “1.jpg”; $imgType = ‘jpg’; #Open picture open(IMG, “$image”) or die; #Open […]