Error not raised as I expect
-
I have just come across a slightly strange occurence in T-SQL and I'm hoping someone can explain it to me.
- I have 2 procedures proc_parent & proc_child. proc_parent calls proc_child.
- I have error handling around each SQL statement in proc_child that checks @@ERROR and will return a 1 if an error occurs, otherwise 0.
- I have error handling around the calls to proc_child which use a GOTO statement to send proc_parent to an error handling block if proc_child returns anything other than 0. Therefore, if proc_child errors the following code in proc_parent should never execute - that is the expected behaviour.
However... proc_child is raising an error (I know what the error is and why it is occuring - that's not the issue that I'm posting about) but proc_parent continues to execute. I have put some print statements into proc_parent & proc_child and have PROVED that proc_child is completely bombing out when the error occurs. @@ERROR never gets checked by the error handling code, proc_child simply stops executing, control returns to proc_parent which carries merrily on its way as if everything is OK.
The error in proc_child is caused by a faulty UPDATE statement that tries to exist a non-existent field.
I guess my question is...why does this UPDATE statement not cause an error to be caught by my error handling code? Any suggestions or ideas would be most welcome!
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
It's due to the error severity
A high enough severity will cause the entire batch to abort so that error trapping code does not execute. Referencing a non-existent column is high enough severity to cause this.
-
PW,
Thanks for that. There's 3 of us here scratching our heads over this and that explains it.
Perhaps you can explain 1 other strange thing that we are seeing. Immediately PRIOR to the failing SQL statement in proc_child I have a "PRINT literal" statement. This PRINT statement does not execute...or more accurately the literal value is not PRINTed to to Query Analyser. There are other "PRINT literal" statements earlier in proc_child that DO return the literal to Query Analyser.
Can you explain that?
Thanks again...its nice to go home at the end of the day knowing you know more than you did at the start of it
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
I've had some instances of this and you are not going to like my answer: "it depends"
The best bet is to debug and try to see if it executes. Sometimes when code like this seem to be close to the error, the optimizations rearrange the order and you get this effects because such failures prevent buffer flushing!
It became very apparent to me when testing with non connected linked servers. Even if the sp had not executed any linked server related statement just calling the sp failed
HTH
* Noel -
Probably the PRINT buffer not flushing before the batch aborts.
Using RaisError with a severity of zero acts like a PRINT, but flushes immediately (or so I read on the MS newsgroups).
RAISERROR('Test message', 0, 1) WITH NOWAIT
-
Noel/PW,
Thanks for the replies. If nothing else its good to know that I'm not going mad - there is something awry in SQL Server here. At least we're aare of it for the future.
Of course, the problem is easily fixed (and we've fixed it), we were just concerned about errors not being handled properly. Not tha we've connected the erroneous code everything is hunky dory.
Thanks again
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Just so you know, the most frantically awaited feature for me from SS 2005 has a name: STRUCTURED EXCEPTION HANDLING
There is nothing like making sure that if something missbehave you are still in control
Cheers!
* Noel -
Excited about TRY...CATCH I imagine? We've just been saying here that we need that.
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Yep, This "feature" has been the Achilles heel of TSQL for ages
* Noel -
Yes, that is something that is long overdue and a feature I am also looking forward to (and getting familiar with by test porting some existing procs to Yukon).
However, I also predict an increase in really bad stored procedure coding - if you think things are bad right now with non-relational-minded folk going crazy with T-SQL cursors, just wait till you see the bad code that gets written once stored procs can be written in .Net languages. Can just see the nested C#/VB.Net loops to take the place of solid set-based T-SQL code. At least it should generate good consulting opportunities to fix the mess ...
-
At least it should generate good consulting opportunities to fix the mess ...
Agreed! I liked that part ... a lot
* Noel -
I imagine that'll be nothing compared to the number of .Net developers that think they can simply bypass a database schema by simply sticking everything in single field of type XML
Again though...lots of consulting services opportunities as you say...which is good for my company..and me
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
If you check you might find that in this case your chid proc is returning NULL for a return code.
If that's the case you can reslove the failure to catch the error by testing the your return code for <> 0 or for IS NULL.
You may have to modify all your return code checking to be something like:
IF @return_code <> 0 OR @return_code IS NULL
raiserror ....
If i'm wrong about this, please jump in and let me know.
Edit: Please note: that the default return value from a sp is 0. So it might be a better idea have your code check for an explicitly coded return value of 1 and consider null or anything other than 1 an error.
EDIT2: As noted in my correction post below, when a proc fails with a severe error the return is not set to null. In fact in such a case it is not set at all and the variable capturing the return value is unchanged (i.e. is still set to what is was prior to executing the sp call).
-
Ron,
I already use the condition @vReturnStatus <> 0 so the extra check for IS NULL isn't necassary.
proc_parent still wasn't going into my error handling block though. This was because @vReturnStatus had already been set to 0 by a previous SP call. I added the line SET @vReturnStatus = NULL imemdiately prior to the call to proc_child and yes....it sent into the error handling block.
There is a lesson here. If you're going to be really strict with yourself then your variable that captures the return value from an SP should be set to NULL each time prior to using it. I'm not going to worry about it though!!
Thanks for your comments!
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
That depends on how you have ANSI_NULLS set. When ANSI_NULLS = ON then a comparison to a null value will yield a result of FALSE and a check for IS NULL will be necessary.
Apparently you are running with ANSI_NULLS = OFF.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply