How to send custom warnings to alert the users or applications.

Problem: How to handle an exception and set custom message as system error or warnings!

Solution: RAISERROR

This T-SQL command can be used to return messages back to the user or the application it has been called from to send as a warning or informational messages based on the conditional checks.

RAISEERROR will be helpful to set a custom message for a particular error handling when used inside try catch statements to trap the exceptions.

This can raise a warning or print an informational message based on the error_severity specified to raise

Errors with severity from 0-10 are informational messages and similar to PRINT, errors with severity between 11 to 16 are shown as a warning and returns as a system error.

Error_severity	actions	Return Output
1-10	will not jump from try to catch	informational message
11-19	will jump from try to associated catch	Warning or System Error
20 and higher	Will terminate the DB connection (sa permissions needed)	Warning and Kill SPID

Example:

In this article I am going to show you how we can return some messages to the user using RAISERROR.

Example 1:

Let’s try to send an informational message with parameter passed from the user to show in the messages.

DECLARE @msg varchar(512)

,@userinput int =-125135

SET NOCOUNT ON

set @msg = '...@userinput value is :' + CAST( coalesce(@userinput,'') as varchar(20))

RAISERROR (@msg, 0, 1) with nowait

OUTPUT :

...@userinput value is :-125135

Example 2:

Let’s try to validate the data for a mathematical calculation to perform A³(A*A*A), raise an error if there is any exception.

DECLARE @msg varchar(512)

,@userinput int =1252

SET NOCOUNT ON

set @msg = '...@userinput value is :' + CAST( coalesce(@userinput,'') as varchar(20))

RAISERROR (@msg, 0, 1) with nowait

                               

BEGIN TRY

                SET @msg = 'User input is does not pass the validation'

                SET @userinput =@userinput*@userinput*@userinput

                IF ERROR_STATE()=1 -- checking the error state to raise error

                BEGIN

                                RAISERROR(@msg,16,1)  with nowait; -- setting the error severity to 16 and so it moves to catch block.

                END

                ELSE

                BEGIN

SET @msg =' The Value has passed test successfully, output = '+ CAST(@userinput as varchar(256))

                                RAISERROR(@msg,0,1)  with nowait;

                END

END TRY

BEGIN CATCH

                SET @msg= @msg+ ',due to the reason: '+ ERROR_MESSAGE()

                RAISERROR(@msg,16,1)  with nowait;

END CATCH

                               

OUTPUT:

...@userinput value is :1252

 The Value has passed test successfully, output = 1962515008

...@userinput value is :125292

Msg 50000, Level 16, State 1, Line 24

User input is does not pass the validation,due to the reason: Arithmetic overflow error converting expression to data type int.