@@ERROR
-
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
-
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
-
Thanks,
I obviously put on my Homer Simpson head on this morning.
J
-
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply