About @@ERROR in SQL 2005 online book

Here

http://msdn2.microsoft.com/en-us/library/ms190193.aspx

it is explained that @.@.ERROR will be cleared and reset.

But here:

http://msdn2.microsoft.com/en-us/library/ms190248.aspx

http://msdn2.microsoft.com/en-us/library/ms187009.aspx

we still see:

IF (@.@.ERROR <> 0)

SET @.ErrorSave = @.@.ERROR

Chester

Is there a question here?

I assume you are asking if @.@.Error is reset during the IF statement. If you read the first link, it says NO.

http://msdn2.microsoft.com/en-us/library/ms190193.aspx

@.@.Error is reset by the next TSQL command. IF is a conditional statement and does not reset the flag.|||

Hi Chester,

The second example is indeed incorrect as @.@.error will be reset to 0 by the IF statement. The local @.ErrorSave should be set after each statement and them compared:

declare @.a int, @.ErrorSave int;

set @.a = (1 / 0); -- divide by zero

set @.ErrorSave = @.@.ERROR;

if (@.ErrorSave <> 0)
begin
select @.ErrorSave AS [ErrorNumber]
end

Cheers,
Rob

|||

Hi Tom,

That's sort of incorrect - the IF will return TRUE when evaluating the statement "IF (@.@.EROR <> 0)", however @.@.ERROR is then reset by the successful completion of the evaluation:

declare @.a int, @.ErrorSave int;

set @.a = (1 / 0); -- divide by zero

if (@.@.error <> 0)
begin
select @.@.error AS [ErrorNumber] -- will return 0
end

Cheers,
Rob

|||

Here

http://msdn2.microsoft.com/en-us/library/ms190193.aspx

It says exactly:

Conditional statements, such as the IF statement, reset @.@.ERROR. If you reference @.@.ERROR in an IF statement, references to @.@.ERROR in the IF or ELSE blocks will not retrieve the @.@.ERROR information. In the following example, @.@.ERROR is reset by IF and does not return the error number when referenced in the PRINT statement.

|||

Thanks, Robert.

Actually I found this problem in one of our partner's application that was wriiten by a local Robert.

When I checked the online book, it was misleading at first .

Chester

|||Yep, you are right, my mistake.

@.@.Error is reset by the IF. The best thing to do is: SET @.errorcode = @.@.ERROR right after the command.

The @.@.Error traps so little errors, it is almost worthless. Most of the time the error in the command just terminates the stored proc and never gets to the error trap anyway, unless you use Try/Catch.sql