RAISERROR:

RAISERROR raise an error with a user defined error message. The error message is either created dynamically or stored in the system table sysmessages.

Example: Lets create a SP, pass 2 number, if second number is 0, then error as .

CREATE PROCEDURE SPDivision @FirstNumber int@SecondNumber int
AS
    IF 
@SecondNumber=0
B
EGIN
SELECT ‘Error’
R
AISERROR (‘Error: Division by zero.’, 16, 1)
     END
   ELSE
     select @
FirstNumber/@SecondNumber

When Run SP as  EXEC SPDivision 5, 0   will show error as

(1 row(s) affected)
Msg 50000, Level 16, State 1, Procedure spDivision, Line 6
Error: Division by zero.

If the error message is used in many SP’s, to avoid inconsistencies due to changes in the message, the message can be stored in sysmessages.. The system SP sp_addmessage can be used to add the message and sp_dropmessage fro drop message. Be sure User-Defined error messages must have a msg_id greater or equal to 50001.

Example: To add an error message into sysmessages..

EXEC sp_addmessage 50001, 16‘Error: Division by zero.’

Now above SP code will be as

CREATE PROCEDURE spDivision @FirstNumber int@SecondNumber int
AS
   IF 
@SecondNumber=0
     BEGIN
       SELECT 
‘Error’
       RAISERROR (50001, 16, 1)
    END
    ELSE
    Select 
@FirstNumber/@SecondNumber 

@@ERROR:

The system function @@ERROR returns an error code if an error was encountered after the completion of the TSQL statement immediately preceding it, otherwise it will return 0, means success. The value of @@ERROR changes for each TSQL statement and the only way to keep track of errors is by using a temporary variable to store the error code. If there is no need to keep track of the error but simply act upon it, then the value of @@ERROR can be checked after the TSQL statement to be tested.

CREATE PROCEDURE spDivision @FirstNumber int@SecondNumber int
  AS
    DECLARE 
@errnum int
    Select 
@FirstNumber/@SecondNumber
    SET 
@errnum=@@Error
IF @errnum<>0
S
ELECT ‘Error’

 EXEC spDivision1 5,0

Msg 8134, Level 16, State 1, Procedure spDivision, Line 4
Divide by zero error encountered.
(1 row(s) affected)

The return code is changed automatically to store the latest @@Error value if no RETURN statement is present. Even if there are more statements after the error occurred, the error code is still preserved.

Advertisements
Comments
  1. your content made me want to read more and more. congratulations for the work. thanks! lista de email lista de email lista de email lista de email lista de email

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s