November 13, 2002 at 12:28 pm
How can I handle an error in sql procedure if the data that is being fetched
by the cursor is being inserted into a table which doesn't allowed duplicates. How can I recover from this error and fetch the next records that the cursor has.
Thanks in advance
November 13, 2002 at 12:43 pm
Check if that record exists in the table into which you are trying to insert.
IF EXISTS(SELECT Column FROM Table WHERE COlumn = @Column)
November 13, 2002 at 2:57 pm
It depends on how your client manages errors. But often the client responds to the first error and does not complete the rest of the code.
This can be forced using SET XACT_ABORT ON
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 13, 2002 at 4:23 pm
Why are you using a cursor to insert records into a table. If it were to be done with a set based operation, you could left join the table being inserted into to find what wasn't already there to insert and never encounter the error you are. It would also only take a percentage of the time and overhead?
I'm just curious? Is there a particular reason?
December 3, 2002 at 8:39 pm
See prior post.
Errors can be trapped and ignored in SQL Server. Also, if possible, avoid the cursor altogether.
December 7, 2002 at 9:56 pm
I have to agree with Scorpion_66, why are you using a cursor instead of set based. Set based in most all (but not all, there are always exceptions) will outperform cursor based. If you want to, post your cursor and let us take a look. Someone might see right off how to make it set based and maybe even speed it up.
As for leaving a cursor and catching the error. It is far cleaner to check for the possibility of an error with the
if exists(.....)
method as menntioned in the other thread http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=8156&FORUM_ID=10&CAT_ID=4&Topic_Title=SSQL+PROCEDURE&Forum_Title=Certification than any other way.
Edited by - antares686 on 12/07/2002 9:57:30 PM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply