ASP Alliance Member: MTG

Don't Use @@ERROR with UPDATE Statements

Using @@ERROR to detect error conditions from Microsoft SQL Server 7.0/2000 can be tricky business. Why? It doesn't always work the way you want it too!

Typically, @@ERROR is used to generate error messages or rollback transactions within T-SQL scripts. Here's an example: -- Perform some SQL stuff here.... IF @ERROR > 0 BEGIN -- Perform some corrective actions if an error occurs where @@ERROR is greater than zero... END The problem with the @@ERROR variable is when you use an UPDATE statement with a WHERE clause. If the WHERE condition is not satisfied, SQL Server will NOT populate the @@ERROR variable with an appropriate integer.

For example, the following code will NOT cause @ERROR to be greater than zero even though the UPDATE command was NOT able to find a match for the column 'firstname': UPDATE mytable SET firstname = 'Michael' WHERE firstname = 'Mike' IF @@ERROR > 0 BEGIN PRINT 'There was an error updating mytable!' END The above example will NOT cause SQL Server to generate an error number in @ERROR that is greater then zero.

There is one way to determine if a successful UPDATE statement occurs: @@ROWCOUNT. Whatever an UPDATE statement successfully updates will cause SQL Server to populate the global variable @@ROWCOUNT with the number of records affected by the UPDATE.

Therefore, if in the above example the statement did find a column name 'firstname' with a value of 'Mike', the UPDATE would succeed and populate the @@ROWCOUNT with an integer of 1 (1 record updated). That is, of course, if only one 'Mike' value existed in the column 'firstname'. If more than one 'Mike' value existed in the column 'firstname', then the @@ROWCOUNT variable would be affected by the number of records updated accordingly.

So, we can modify the original example of using @@ERROR like this: UPDATE mytable SET firstname = 'Michael' WHERE firstname = 'Mike' IF @@ROWCOUNT > 0 PRINT 'mytable was updated successfully!' ELSE PRINT 'There was a problem updating mytable!'

Comments & Questions Form Comment(s):