Tips and Tricks for:  SQL Server T-SQL

The Problem:   Testing @@Error and  @@RowCount one after the other.

T-SQL's @@ERROR function is reset after every statement and so is @@RowCount.  It's so easy to write incorrect T-SQL that tests one of these two functions in one statement and then tests the other one in the next statement.  This script illustrates:

declare @i int
select @i = 3/0
if @@Rowcount = 99 SET @i = 99
print '@@Error=' + coalesce(convert(varchar(20), @@Error), '<null>')
         +' @i=' + coalesce(convert(varchar(20), @i), '<null>')
print ' '
go
declare @i int
select @i = 3/0
print '@@Error=' + coalesce(convert(varchar(20), @@Error), '<null>')
         +' @i=' + coalesce(convert(varchar(20), @i), '<null>')
go
 

This is what you get:

Server: Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.
@@Error=0 @i=<null>

Server: Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.
@@Error=8134 @i=<null>

As you can see in the first batch execution of the IF statement reset @@Error.  In the second batch where the IF statement was removed, @@Error has the correct error code for the divide by zero.

Solution: Save @@Error and @@RowCount after every statement.

As the script above shows, doing any thing after a SQL statement resets @@Error.  The solution is to save both in a select statement after each significant SQL DML or DDL statement in a procedure.

DECLARE @myError int, @myRowCount int

-- Do your SQL DML statement or SQL DDL statement here....

SELECT @myError = @@Error, @myRowCount = @@RowCount

Since neither function is reset until after the entire SELECT is completed, you can capture both for  later use.  Yes, this is tedious but in T-SQL it's the only way.