Hi, I have a cursor that inserts one row at a time on a transaction level. How can I code it so that if an error is encountered for one specific row then that row is rolledback and the cursor continues on to the next row? So far I have the following which appears to exit out the cursor when the error is occured so no subsequent rows are inserted:
All help greatly appreciated.
asked Dec 15, 2010 at 07:32 AM in Default
David 2 1
Why do you do RETURN after the rollback? What happens if you remove that?
I'm not sure myself, I can't remember how error handling was done in the mean old days.
answered Dec 15, 2010 at 08:13 AM
Considering that per David's comment
there are a couple of options to consider. Use whichever one seems to be more convenient:
Comment out the word reading RETURN which is after the line reading ROLLBACK TRAN. You correctly designed the script to have each insert as a separate transaction, so the cursor loop can continue but then you prevent the continuation from happening by forcing the batch to bail out (return)
Outsource the activity to the stored procedure, which will make you code looking like this:
The above implies that youi have to have the proc doing the insert, something like this:
Error handling in SQL 2000 is quite primitive, you may be better trying that in a procedural language (C#, VB.Net etc.)
However, the way you are doing that insert is going to hurt your performance in a big way.
It would be better for you to change the code into:
This will run faster and cause less "fluff" in yout log file. Each step in your cursor has an explicit transaction that has to be logged, bulking the insert in the way illustrated will reduce that.
answered Dec 15, 2010 at 07:40 AM