Generate Error Message In SQL Server 2008

In this article I will explain how to invoke a error message using RAISERROR command.
  • 3243

In this article I will explain how to invoke a error message using RAISERROR command.

RAISERROR command is used to generate an error message either from the sys.message system catalog view or error message produced from a string at run time.

RAISERROR command has three primary components: error number, severity and state. According to msdn RAISERROR is used to, Help in troubleshooting Transact-SQL code, Check the values of data, Return messages that contain variable text, Cause execution to jump from a TRY block to the associated CATCH block, Return error information from the CATCH block to the calling batch or application. The syntax of RAISERROR is:

Syntax

RAISERROR({msg_id|msg_str|@local_variable}

{,severity ,state}

[,argument[,...n]])

[WITH option[,...n]]

In above syntax,

First parameter include msg_id, msg_str and @local_variable, you can choose one of three options. The msg_id specifies error id which is supplied by user which can be between 50,001 and 2,147,483,647. The msg_str is a user defined message with a limit of 2.047 characters. The @local_variable is a string variable which is used to pass this message string.

Second parameter is severity and state. The severity  defines severity level of your message which can be between 1 and 25. The state is a user defined number(1 through 25) for identifying the location of failing code.

argument[,...n] specifies substitution parameter that is used in the error message.

WITH option[,...n] includes three options i.e. LOG, NOWAIT and SETERROR. LOG writes error, NOWAIT sends message to client and SETERROR sets @@ERROR.

Generating error message using RAISERROR

In this example I create a stored procedure to insert a record in Student1Details table. While inserting a record into Student1Details table, Branch will be evaluated first to see whether it is "EN" Branch. If it's not a EN Branch then record will not inserted and error using RAISERROR will be invoked. Suppose we have following table:

Student1Details Table

Table.jpg

Now create stored procedure to invoke error message.

CREATE PROCEDURE sp_insert

@ID int,

@Name varchar(50),

@Branch varchar(5),

@Location varchar(15)

AS

IF @Branch = 'EN'

BEGIN

INSERT INTO [Student1Details]

VALUES (@ID, @Name,@Branch,@Location)

END

ELSE

BEGIN

RAISERROR(' No new studentdetails can be added for %s Branch',

16,1,@Branch)

END

Execute sp_insert stored procedure.

raiserror-command.jpg

Working

This example used RAISERROR command to generate an error if a condition is not met  defined in a IF condition. In above stored procedure, value of Branch is checked. If its value is other than EN Branch, record will not inserted.

IF @Branch = 'EN'
BEGIN
INSERT INTO [Student1Details]
VALUES (@ID, @Name,@Branch,@Location)
END

Now IF condition does not satisfied because Branch was "EI", the ELSE clause invokes the RAISERROR command.

ELSE
BEGIN
RAISERROR(' No new studentdetails can be added for %s Branch',
16,1,@Branch)
END

' No new studentdetails can be added for %s Branch' is the first parameter which includes one substitution parameter for Branch name. The second parameter 16 is severity level. The third parameter, 1, is the state and last parameter @Branch is substitution parameter included in error message.

© 2020 DotNetHeaven. All rights reserved.