@@ERROR VS RAISERROR
Difference between @@error and Raiserror in sql
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:
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:
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
Now above SP code will be as
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 @errnum=@@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: