Error handling question

Question

Error handling question

She Through Whom All Data Flows

Ten Centuries

Points: 1209
More actions
July 23, 2009 at 1:52 pm

#208442

I'm trying to get better at implementing error handling in my stored procedures. I've just learned about the TRY...CATCH method and it looks useful so will likely start using it. However, can someone explain to me why the following doesn't work?
declare @delErr int,@inserr int
set @delerr=0
set @inserr=0
--set xact_abort off
begin tran
delete from aoiprograms
set @delerr=@@error
print @delerr
if @delerr<>0 goto ErrHandle
INSERT INTO AOIPrograms ( noun, tag, LastTestDate )
SELECT Panels.noun,
case
when tagset is null then 'none'
else tagset
end
as tag,
--Max(Panels.testdate) AS LastTestDate
'this is not a date'
FROM Panels
GROUP BY Panels.noun,
case
when tagset is null then 'none'
else tagset
end;
set @inserr=@@error
print @inserr
if @inserr<>0 goto ErrHandle
commit transaction
set @vmsg='tranaction committed'
return
ErrHandle:
print N'error handling in effect!'
if xact_state()=-1
begin
rollback transaction
set @vmsg='transaction rolled back'
select @vmsg
end
return @vmsg
END
Field LastTestDate is a date field, so I've set this test up so that it will always return an error, as I'm tyring to put a string into that field. When it fails, I want it to print out "error handling in effect" and return "transaction rolled back". But instead, the only return I get is the full SQL-generated error:
0
Msg 241, Level 16, State 1, Procedure ErrorHandlingTest, Line 37
Conversion failed when converting datetime from character string.
I know my transaction isn't being committed because I can check the table. Why doesn't the code ever seem to make it to ErrHandle? Why don't my PRINT statements print anything?
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]

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

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

It's an actual execution error, which means it doesn't execute anything after the initial error. Meaning - never mind the GOTO's, it's going nowhere after the error.

This is precisely the kind of erro the TRY/CATCH was built for.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

She Through Whom All Data Flows Ten Centuries Points: 1209 More actions · Answer 2

Can you elaborate on which kinds of errors I would expect to be able to trap using @@error, and which ones I wouldn't? That is, what's the exact definition of an "execution error"? As opposed , say, to a "runtime error".

I see all these examples in the books I have, where they are doing something like this. I can think of several ways for the insert statement to fail. WHich ones would

I expect to activitate my {do some stuff}:

1. PK violation where key already exists in the table? (in which case my "error handling in effect" does get printed out, but I get another error saying Commit or Rollback is missing)

2. PK violation where key is duplicated in incoming data?

3. Data type violation (as illustrated, I guess the answer is no)

4. Table AOIPrograms doesn't exist (looks like the answer is no here too)

[font="Comic Sans MS"]She Through Whom All Data Flows[/font]

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

@@error is fairly limited in what it can do for you. It seems to be useful in catching informational messages, or messages that have been determined not to be "batch-interrupting" (meaning - it stops as soon as it runs into this kind of error). In general, that scales with the error "level" or severity: as I recall, 1-2 is just information, 3-11 warning, 12-15 various flavors of non-critical errors, and 16+ = "bad" errors. Since user-raised errors are also 16, some are batch-terminating and some not.

Unfortunately - validation errors tend to be batch-terminating. This error (where the data isn't even close to being right for the field) doesn't allow you to continue; had it been a really old date (so that it causes an out of bounds error), the rest would execute.

The point is - @@error was fairly finicky, and did not give you much room to actually stop your job from just crashing. TRY/CATCH gives you a LOT more options as to what you can prevent.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

She Through Whom All Data Flows Ten Centuries Points: 1209 More actions · Answer 4

Thanks much, I'll proceed along with Try--Catch instead.

[font="Comic Sans MS"]She Through Whom All Data Flows[/font]