Error Trapping in scripts

[onedunpark]

Hi,

Trying to build error-trapping into my triggers.

All of this is being done in Query Analyzer, prior to actually amending the triggers.

These commands work, evaluating the error variable correctly as 0 and displaying 'succeeded' as required.

DECLARE @IERROR INT

INSERT INTO ERRORTABLE (INDEXVAL,TABLENAME) VALUES (1, 'STEVEN')
SELECT @ierror = @@ERROR
IF @ierror <> 0
print 'failed'
ELSE
PRINT 'succeeded'

However, when deliberately removing the second value in the 'values' clause, (to ensure that the error-trap does detect the error), I do get a system message that the columns/values mismatch (as I would expect) but the code does not continue to the error evaluation.

This is no doubt elementary, but I can't suss what to do to have my code continue after the error to actually try and trap it.

Many thanks for any and all replies

Steven

 

[LFCfan]

i would think (although i'm sure someone will correct me in the likely event that i am wrong) that the reason this happens is that the error is syntactical rather than an actual insert error (for example when 'ab/cd/efgh' is trying to be inserted into a datetime field). In which case the statement cant be compiled...?

I have used that sort of thing in the past (inside stored procs) and it does work like you want it to. I cant test it out at the moment, but perhaps if you try an actual insert error as above and see whether it works...?

HTH
LFCfan

 

[LFCfan]

i've just tried the following.
executing the procedure returns

Server: Msg 220, Level 16, State 2, Procedure blah, Line 6
Arithmetic overflow error for data type tinyint, value = 1000000.
The statement has been terminated.
failed

so it does work as you want it to in this case

***************************************************
alter proc blah
as
declare @err int
create table #tmp(col1 int, col2 tinyint)

insert into #tmp (col1, col2) values (1,1000000)
set @err = @@error
if @err <> 0
print 'failed'
else
print 'succeeded'
drop table #tmp
go

exec blah

 

[onedunpark]

Hi,

Tried it as you have it and it worked fine. Thanks.

Funnily enough, I also tried it passing a text value to a datetime column and it failed for me without evaluating the condition (which was along the same lines as my original problem), returning the following message:

Server: Msg 241, Level 16, State 1, Line 4
Syntax error converting datetime from character string.

It gives me something to work with for the meantime though.

Thanks again for the reply

Steven

 

[LFCfan]

Hmm, I see what you mean.
No idea beyond what i've already posted though - sorry!