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:

1_SQL_Server_@@error_System_Function

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:

2_SQL_Server_@@error_System_Function

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

   

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

Leave a Reply

Your email address will not be published.