@@error trap not working properly

[Eyespi20]

Let me preface this request for help with the disclaimer that @@error is somewhat of a mystery to me. I understand somewhat what it does, but using this function has proven to be tricky.

BEGIN TRANSACTION
insert into request_event select
	externalrefid,
	sequence,
	security_code,
	request_line,
	req_line_sequence,
	event_type,
	event_type_reason,
	event_dt,
	event_tm,
	customer_event_dt,
	customer_event_tm,	
	person_id_from,
	person_id_to,
	description,
	dur_minutes,
	value_from,
	value_to,
	value_type,
	promised_begin_dt,
	promised_begin_tm
from
	newevent2
where
	chk=0


IF @@ERROR <>0
BEGIN

update econupdate set chk=2 
from econupdate join newwork on econupdate.externalrefid=newwork.externalrefid
and econupdate.econupdate_identity=newwork.updateindex


exec master..xp_sendmail
	@recipients='margaretn@ttc-inc.com',
	@subject='Event insert step failed',
	@query='select * from econupdate where chk=2',
	@attach_results='true',
	@width=250


ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION



end

When an error occurs, I don't get the expected email nor does the update to the row checkpoint occur.

Can anyone tell me what I did wrong?

Thanks!

Margaret

 

[SQLSister]

fisrt, alawys set the value of @@error to a variable.
then print that value out before you run the if statment (which should now use the variable). You may find that no error was generated.

Questions about posting. See faq183-874

 

[Eyespi20]

Well, I do know an error was generated as the job failed, but I will do that (assign the error to a variable).

Thanks SQLSister!

Margaret

 

[Crowley16]

you are housing your error handling code within the transaction itself, and right at the end of it, you do a rollback, therefore, you have just undone all your error handling code, and left the proc without doing anything apart from waste processor time...

try using this format:

begin tran a

if @@error = 0 begin
commit tran a
end
else begin
rollback tran a

--send your emails and stuff
end

--------------------
Procrastinate Now!

 

[Eyespi20]

Thanks Crowley -- I thought that might be happening, but didn't really know how to get around it.

Margaret