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.
