Retrieving @@ERROR and @@ROWCOUNT After Operation

[Hexonx]

Here's a helpful tip that TomSARK gave to me. In my procedure, I update a table, then, if the table was actually updated, I insert an audit record. But if an error occurred, I want to trap it. My problem is in evaulating both @@ERROR and @@ROWCOUNT because using SET with one would reset the other.

Here's the original:

-- Update the table
SET @SQL = 'UPDATE MyTable ...'

EXEC( @SQL )

SET @Err = @@ERROR -- Gets error status from EXEC(), but resets
@@ROWCOUNT to 1
SET @RowCount = @@ROWCOUNT

IF @Err <> 0 GOTO ErrHandler

IF @RowCount > 0
-- Insert audit record

Here's what works:

-- Update the table
SET @SQL = 'UPDATE MyTable ...'

EXEC( @SQL )

SELECT @Err = @@ERROR, @RowCount = @@ROWCOUNT

IF @Err <> 0 GOTO ErrHandler

IF @RowCount > 0
-- Insert audit record

Many thanks to TomSARK