@@ERROR logic not working

Hi There

I am having a little trouble with something very simple:

I have a stored procedure with the following sql in it:

UPDATE mfsWS_CustNewCustomer SET Result_Id = 0 WHERE QueryGUID = @.Guid

IF (@.@.ERROR <> 0)

BEGIN

RAISERROR ('Error setting Result_Id', 16, 1)

UPDATE mfsWS_CustNewCustomer SET Result_Id = 1 WHERE QueryGUID = @.Guid

RETURN

END

To test if this works i SET the Result_Id = 'X' this is a smallint column so this should not work.

My issue is that all i get is the error:

cannot convert char to numeric etc etc

However the @.@.ERROR logic is not executed , my error is not raised and the update does nto happen, it si as though the entire sp aborts at the point where i try do the bogus update.

Is there something i should SET at the beginning of my sp or something ? How do i ensure that any error will be passed to the logic that checks @.@.ERROR and performs the appropriate actions ? This is SS2000, so i cannot use try catch.

Thanx

Unfortunately, you can't. There isn't a way to trap any and all kinds of errors in your T-SQL code, since some errors terminates the batch. T-SQL code after the point where the error happens, never gets executed. In your case, a conversion error are of the 'breaking' kind.

You can verify with a small example. Note that the last line never gets printed.

declare @.i int
set @.i = 'x'
print 'Error happened'
go

Server: Msg 245, Level 16, State 1, Line 2
Syntax error converting the varchar value 'x' to a column of data type int.

In order to trap all kinds of errors, you need to trap the at the client (calling side) of the procedure. (if there is a 'client' of some sorts)

You can find more info on errorhandling here.
http://www.sommarskog.se/error-handling-II.html

/Kenneth

|||

Thanx Kenneth

So what can i do ?

What is the point of doing a IF (@.@.ERROR) check after a delete update or insert then?

I have to update a certain table to set the error code to 1 if an error occurs, is there no way to do this?

Thanx

|||

Well, it's a bit of a tight spot. T-SQL errorhandling possibilites are somewhat limited pre SS2005. The one certain point that all errors go to is to the 'client'. In the case of a procedure, the 'client' is whatever executed it. If this is another proc, then you still have the same problem. If it's some other 'external' program, then that's where all errors will land.

Please take some time and read through the link about errorhandling above. It's well spent time, I assure you. Errorhandling is like any other 'project', it needs afterthought and desicions must be made about what to do in various situations. It needs to be designed with understanding in order to serve it's purpose best - to give us robust and reliable code.

In this case, I don't think that the mission is impossible, but rather not as straightforward as we would like, due to the limitations and behaviour of some errors in T-SQL. But, to grant yourself some insight, and possibly a direction to go on how you want to handle your situattion, http://www.sommarskog.se/error-handling-II.html is a good place to start. =;o)

/Kenneth

|||

Thanx Kewin

I will definately read the link.

Yes it is a problem i have an sp that calls an sp, i have written it so that if @.@.ERROR <> 0 i RETURN @.@.ERROR and handle it in the calling sp, but in this case the execution will abort and not return the @.@.ERROR to the calling sp to be handled.

But let me first read the link and i will post again.

Thank You

|||

Hi Kewin

Am i correct in saying that my specific test which has to do with char to numeric implicit cast will cause execution to abort BUT if the update or insert for example violated a primary key constriant or something more general that the execution would continue and execute the T-SQL in the IF @.@.ERROR <> 0 logic ?

|||Correct. Not all errors are transaction aborting or batch aborting errors. For example, syntax errors or compile-time errors cannot be caught with exception handling in SQL Server 2005 also. Please check out Erland's home page and the error handling topics in BOL.|||

To add a pennies worth, the big twist to error handling (even in 2005, though you have the control of try...catch) is the client. If you have a well behaved client like Query Analyzer or SSMS, then the IF@.@.error logic is very important, because it will always return control after an error if the error is not batch terminating. However, almost every client I see stops executing on an error and ends the batch itself. In those cases it is important to handle the errors with that in mind.

Things like

<DML operation>

if @.@.error <> 0
begin
raiserror ('boom',16,1)
rollback transaction
return -100
end

Look good, but if the client never completes the message and returns control, you can be stuck with an open transaction. And even worse, you probably never reach the raiserror, rollback, or return if the error in the DML operation occurs.

But you have to be ready for it nevertheless...

|||

Thanx for all the feedback guys.

I have read the link Kewin and i know most of it but l did learn one or 2 new things, thanx.

I guess what i am really trying to ask is as far as SS2000 goes there is not anything more i can do in an stored procedure than :

IF @.@.ERROR <>0

BEGIN

RASIERROR

ROLLBACK TRAN

RETURN -1

END

These sp's are called by a .Net app i understand completely that the app must be ready for any kind of exception.

What i really want to confirm is that there is nothing more or better i could do from an sp error handling point of view?

Thanx

|||

You got it.
Basically, that's what is avaliable to us as far as errorhandling in T-SQL on SS 2000 goes.

A workaround of sorts around these limitations can be done, and when I think about it, that's pretty much what 'scrubbing' is all about. For known 'fatal' errors that you know you can't trap, and possibly could leave you in some sticky situation, it's common to try to ensure that the situation never happens, either by first validating thoroughly, or by different coding techniques.

eg, to avoid a PK violation when inserting a new row, you could first check for the existence, or (perhaps even better) do the insert based on a left joined select. In the latter case, if the PK didn't exist, 1 row would be affected, but should it already exist, 0 rows would be affected. But more importantly, it's not an error to do some DML and have zero rows affected. Approaching a problem from different angles may be of aid to circumvent some of the errorhandling limitations that we have.

/Kenneth

sql