Header Ads

@@ERROR VS RAISERROR

Difference between @@error and Raiserror in sql

SQL-@@error

What is @@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.

Syntax:
CREATE PROCEDURE spDivision @FirstNumber int, @SecondNumber int
  AS
    DECLARE @errnum int
    Select @FirstNumber/@SecondNumber
    SET @[email protected]@Error
IF @errnum<>0
SELECT ‘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.

What is 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.

Syntax:
CREATE PROCEDURE SPDivision @FirstNumber int, @SecondNumber int
AS
    IF @SecondNumber=0
BEGIN
SELECT ‘Error’
RAISERROR (‘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 

No comments:

Powered by Blogger.