SQL Server @@error System Function

Hi Folks,

You would have seen my blog which was a brief description about the significant system functions.

I have made some keynotes about the SQL Server @@error system function. They are as follows:

• It returns the error number for the last Transact-SQL statement executed.
• The return type is Integer, and while 0 indicates success.
• This system function holds the last error in a variable until another error occurs, and is updated for every command.
• @@error is set to 0 if the statement executed successfully, otherwise it will report an error and the error message is returned.
• We can also view the text associated with the @@error error number in the “sysmessages” system table.
• Since @@error is cleared and reset on each statement executed, so it would be better if we save it to a local variable that can be checked down later.
• This will be more clear if you see this example:

I have used a table “Students1” from a database “TEST”, you can see it from below:

Here, SID is a Primary Key. So if we try to update the value of SID which is already in use, it will generate an error, because it is against the rule of Primary Key.

And also it will prove useful if we save it to a local variable ‘@var’;

USE TEST
DECLARE @var INT;
 
UPDATE Students1
SET SID = 3
WHERE SID = 4
SET @var = @@ERROR
 
PRINT @@ERROR;
 
IF @var <> 0
BEGIN
PRINT @var;
END;

The result that we have got is that:

It says that:

Msg 2627, Level 14, State 1, Line 4

Violation of PRIMARY KEY constraint ‘PK__Students__CA1959700EA330E9’. Cannot insert duplicate key in object ‘dbo.Students1’.

The statement has been terminated.

0

2627

Well this is all about @@error System Function.

And also comments on this!!

Regards

Piyush Bajaj

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook