@@ERROR

Question

Post reply

@@ERROR

JulieW

Say Hey Kid

Points: 680
More actions
November 5, 2003 at 7:53 am

#160344

Dear All,
I am trying to write a sp which on error will capture the error in a declaration. However the trace results in 0 being passed to variable @ErrorSave though it is been passed the value from @@ERROR.
Could some bright person see what the matter is ?
Thanks
J
Please find the code
create table show_error
(
ID int not null PRIMARY KEY,
col1 int
)
create procedure p_JTest as
DECLARE @ErrorSave INT
SET @ErrorSave = 0
BEGIN TRANSACTION
INSERT INTO show_error values (1, 1)
if @@ERROR <> 0
BEGIN
SET @ErrorSave = @@ERROR
GOTO TRAN_ABORT
END
INSERT INTO show_error values (2, 2)
if @@ERROR <> 0
BEGIN
SET @ErrorSave = 1
SET @ErrorSave = @@ERROR
GOTO TRAN_ABORT
END
INSERT INTO show_error values (2, 2)
BEGIN
SET @ErrorSave = 1
SET @ErrorSave = @@ERROR
GOTO TRAN_ABORT
END
if @@ERROR <> 0 GOTO TRAN_ABORT
BEGIN
SET @ErrorSave = 1
SET @ErrorSave = @@ERROR
GOTO TRAN_ABORT
END
COMMIT TRANSACTION
goto FINISH
TRAN_ABORT:
ROLLBACK TRANSACTION
GOTO FINISH
FINISH:
SELECT * FROM show_error

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

DavidT SSCommitted Points: 1727 More actions · Answer 1

Your @@Error number is being lost with the if @@Error <> 0 begin part (ie: this part does not produce any errors).

Pass the @@error into another variable immediately after the inserts and then use this variable in the if and set part:

declare @ErrorNo int

insert...

set @ErrorNo = @@Error

if @ErrorNo <> 0

begin

set @ErrorSave = @ErrorNo

end

Edited by - davidt on 11/05/2003 08:04:59 AM

JulieW Say Hey Kid Points: 680 More actions · Answer 2

Thanks,

I obviously put on my Homer Simpson head on this morning.

J

paulbowman Mr or Mrs. 500 Points: 572 More actions · Answer 3

Don't forget if you need the @@ROWCOUNT as well you need to use a SELECT not a SET.

Classic newbie error:

UPDATE ... Transaction...

SET @Err_Save = @@ERROR

SET @Row_Save = @@ROWCOUNT

@Row_Save will always be 1 (number of rows affected by preceeding SET @Err_Save... statement) Instead capture all needed transients at once with:

SELECT @Err_Save = @@ERROR, @Row_Save = @@ROWCOUNT

I've seen the first error create merry havoc, especially given the psychological tendency to capture the @@ERROR first when combined with an expectation that the Rowcount should be 1 in the normal case. I've seen developers totally baffled because they haven't realise that their T-SQL has updated X rows instead of the single row update they think they've established.