asked
How To Continue Cursor Loop When Individual Transaction Rolledback?
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: DECLARE @client INT DECLARE cur_1 CURSOR FAST_FORWARD FOR SELECT client FROM TEST OPEN cur_1 FETCH NEXT FROM cur_1 INTO @client WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRANSACTION INSERT INTO BIGTABLE SELECT * FROM SMALLTABLE WHERE CLIENTNO = @clientno IF @@ERROR != 0 BEGIN ROLLBACK TRANSACTION RETURN END ELSE COMMIT TRANSACTION FETCH NEXT FROM cur_1 INTO @client END CLOSE cur_1 DEALLOCATE cur_1 All help greatly appreciated.
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.
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: INSERT INTO BIGTABLE SELECT * FROM SMALLTABLE st INNER JOIN TEST t ON st.ClientNo = t.Client 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.
Considering that per David's comment > Procedural and moving this database to > 2005 isn't an option here 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) OR Outsource the activity to the stored procedure, which will make you code looking like this: DECLARE @client INT DECLARE cur_1 CURSOR FAST_FORWARD FOR SELECT client FROM TEST OPEN cur_1 FETCH NEXT FROM cur_1 INTO @client WHILE @@FETCH_STATUS = 0 BEGIN exec dbo.usp_BigTableInsert @client FETCH NEXT FROM cur_1 INTO @client END CLOSE cur_1 DEALLOCATE cur_1 The above implies that youi have to have the proc doing the insert, something like this: create proc dbo.usp_BigTableInsert (@clientno int) as begin begin tran INSERT INTO BIGTABLE SELECT * FROM SMALLTABLE WHERE CLIENTNO = @clientno IF @@ERROR != 0 BEGIN ROLLBACK TRAN RETURN END ELSE commit tran end Oleg
Yep, I've been there myself :) Though it was good that you confirmed what I was suspecting.
