Help me understand @@error
-
Given the following code from a stored proc:
declare @variable as VARCHAR(6), @SError int, @Stable varchar(50)
set @variable = (select col001 from myDatabase.dbo.myTable)
if @@ERROR <> 0
begin
set @sError = @@ERROR
set @Stable = "Failed during check"
--other unrelated code here--
return @sError
end
Please help me evaluate when @@ERROR is reset.
does @@ERROR get reset to the status of the IF command (thus becoming useless on the following 'set command'?
-
You are correct. What you want to do is to assign @sError = @@ERROR and then test for @sError <> 0.
-
Yes, @@ERROR is reset with *every* command, so it may be easiest to get used to always catch @@ERROR into your own local variable - then do whatever errorchecking stuff.
(in the way John described above)
/Kenneth
-
Well, that's what I was taught, but thought I'd ask you guys for a reality check. I just now started getting into some of the procs here at work and it looks like it's used *wrong* in a LOT of places.
Thanks for the quick reply.
Oh, one more clarification.
given:
exec storedProc 'val1', 'val2'
Am I correct in saying that checking @@error immediately following that command, that @error would only report on the success of the 'exec' command, and not the proc itself?
Bob McC
-
In that case, I usually check both @@ERROR and the return value from the stored procedure.
DECLARE @intReturnValue int
EXEC @intReturnValue = proc_Call_Some_StoredProcedure
SET @intSQLErrorNumber = COALESCE(NULLIF(@intReturnValue,0),@@ERROR,1001)
IF @intSQLErrorNumber <> 0......
-
Yes, that's correct.
declare @err1 int, @err2 int
EXEC @err1 = myProc @val1
SET @err2 = @@ERROR
..now @err1 will contain the return code from the proc
@err2 will contain the result of the statement 'EXEC @err1 = myProc @val1'
(as you suspected)
The method to trap returncodes from inside procs is by this syntax:
EXEC @var = proc
If you only do EXEC proc, then you can't trap the procs return code, only the @@error about the exec proc statement itself. (which may not be the same)
/Kenneth
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply