December 4, 2002 at 4:55 am
Hi people,
Having trouble with my error handling.
I have If(@@Error <> 0)
Goto ErrorHandle
In the label ErrorHandle
I process the error but then I want to Return (0) to the calling proc so that the next transaction can execute, but it just exists the proc with the error message.
What am I doing wrong?
Can someone show me.
Edit: Just thought I should mention that this is after
exec(@InsertString) where @InsertString in an sql insert command dynamically build
Ken
If you don't ask you never get an anwser.
Edited by - the scarecrow on 12/04/2002 04:57:48 AM
If you don't ask you never get an anwser.
December 4, 2002 at 5:20 am
Not all errors can be handled inside your stored procedure.
It all depends on the severity level of your error. In a lot of cases you will never reach the statement that checks @@error because SQL Server will abort your stored procedure when it gets the error. Some sever errors will even terminate the connection.
E.g. a severity level of 19 or higher will stop the current batch.
See BOL for details.
December 4, 2002 at 5:25 am
Thanks for you quick reply.
I figured something like this must be happening.
If I get a truncation error it all works, but if I get an error saying that the string can not be coverted to an int it ends.
How can I handle this?
Ken
If you don't ask you never get an anwser.
If you don't ask you never get an anwser.
December 4, 2002 at 5:32 am
I thought I would post the actual error
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to int.
Now my understanding is that because this is a level 16 error, my error handling should take care of this and proceed.
Correct ?
Ken
If you don't ask you never get an anwser.
If you don't ask you never get an anwser.
December 4, 2002 at 9:54 am
Pre-validate you data before attempting the conversion.
IsNumeric() would work in your example.
December 4, 2002 at 10:00 am
Afraid not. In most cases, a severity level of 16 or above is deemed a fatal error that won'r reach your test for @@Error.
However, some level 16 errors are not fatal e.g. Errors 515, 544, 547 and 550.
Error handling in SQL Server is not an exact science!!
December 4, 2002 at 11:01 am
To add to what's already been answered, you need to use caution and preserve your actual error code. ALL t-sql statements reset @@ROWCOUNT and @@ERROR. This includes your IF statements.
Use something like this to capture the values and save for later
declare @myErr int, @myCount int
[ do something ]
select @myErr = @@Error, @myCount = @@ROWCOUNT
If @myErr != 0
begin
handle error
use @MyErr and @MyCount here to report what happened
end
As already noted, severe errors cause immediate termination of a stored proc.
March 27, 2003 at 4:40 pm
Hi everyone,
I am relatively new to this forum and I tried very hard (for the past hour) to find an answer to my question, but to no avail. On the subject of handling an error, I was wondering whether a duplicate key violation (error 2627) is one that could be recovered from.
Some background:
I am inserting rows into a log table which has a trigger (after insert) which does some processing on the inserted table and inserts a row into another table which uses a primary key. Since I will seldom see a re-insertion of a row that would violate the primary key constraint, and since I needed to speed up my application as much as possible, I decided to try an optimistic approach and do an insert and then recover if there was an error. I saw in the forum that someone had suggested making a unique constraint (as opposed to primary key) and then saying ignore dupes. The problem is that I need to in fact replace the row if one with the same primary key exists. I was doing this by checking for an error (in those rare circumstances) and then replacing the offending row. The problem is that execution never gets to that step.
In case it matters, I am inserting into the first table (the one with the after trigger on it) by running another stored procedure from a Query Analyzer window that does a row by row insert.
I would appreciate any solutions/suggestions that may come to mind.
Regards,
Mazi.
March 28, 2003 at 2:02 am
quote:
Hi everyone,I am relatively new to this forum and I tried very hard (for the past hour) to find an answer to my question, but to no avail. On the subject of handling an error, I was wondering whether a duplicate key violation (error 2627) is one that could be recovered from.
Please don't cross-post your queries in multiple threads. This question is asked in another thread where the discussion should be continued: http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=10928&FORUM_ID=23&CAT_ID=2&Topic_Title=handling+primary+key+violation+error&Forum_Title=General
Regarding the question in this thread, ScareCrow, you shouldn't be getting a datatype conversion error in the first place, you should be able to fix your code for that. What does the dynamic sql string look like?
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 28, 2003 at 10:53 am
Thank you. I continued the topic in the other thread. The other thread, however, has disappeared from the list of active threads under the General topic. I guess this means no one can see it, unless they follow the link you provide in your post. Any suggestions?
Mazi.
March 29, 2003 at 2:28 am
Mazi, I think the active threads show only those threads that have received a new post since your latest visit. So it is only for you it is not visible (until someone posts in it), for me it is visible since you posted a message in it.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 31, 2003 at 9:56 am
quote:
declare @myErr int, @myCount int[ do something ]
select @myErr = @@Error, @myCount = @@ROWCOUNT
The above is correct - look out for developers doing things like:
[do something]
set @myErr = @@ERROR
set @myCount = @@ROWCOUNT
As I found some T-SQL virgin developers doing. Of course the assignment of @@ERROR to your local variable is in itself a T-SQL operation and resets @@ROWCOUNT making any following logic that tests this value invalid.
N.B. Once coders settle on SET as the T-SQL assignment command, going back to using SELECT for simultaneous assignment of multiple variables can often disturb their (non SQL) coding aesthetic - keep an eye out for serial SETs.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply