Working with @@ERROR and @@ROWCOUNT
When you are writing T-SQL code that needs to check for both errors and the number of rows affected after your SQL statements, one of the common pitfalls is trying to get both the error status and the number of rows after a SQL statement runs. You have to remember that all SQL statements except the DECLARE statement reset the value of @@ROWCOUNT and @@ERROR to the status of the last command executed.
If after a SQL statement you check the value of @@ERROR, the statement used to check @@ERROR resets @@ROWCOUNT. If you check @@ROWCOUNT first, it resets the value of @@ERROR. To check both values, you need to use an assignment SELECT immediately after the SQL statement you are checking and capture both values ...
Get Microsoft® SQL Server 2008 R2 Unleashed now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
