x

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.

more ▼

asked Dec 15, 2010 at 07:32 AM in Default

avatar image

David 2 1
732 54 59 65

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Dec 15, 2010 at 08:13 AM

avatar image

Magnus Ahlkvist
21.4k 19 39 42

@Magnus I believe that you are absolutely correct, the RETURN is a deal breaker here, cannot continue after returning.

Dec 15, 2010 at 08:19 AM Oleg

@Oleg thanks for confirming that :)

Dec 15, 2010 at 08:21 AM Magnus Ahlkvist

Thanks everyone, it was the RETURN that was throwing it.

Dec 15, 2010 at 08:22 AM David 2 1

I couldn't see the wood for the tress and selected this answer purely on it coming a few seconds before Oleg. :) Thanks all.

Dec 15, 2010 at 08:41 AM David 2 1
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Dec 15, 2010 at 08:14 AM

avatar image

Oleg
17.2k 3 7 28

Thanks. I'll look into this.

Dec 15, 2010 at 08:23 AM David 2 1

@Magnus Ahlkvist These are the wonders of asynchronous operations. If I saw your answer, I would not submit mine as you already found the source of the problem :)

Dec 15, 2010 at 08:24 AM Oleg

Yep, I've been there myself :) Though it was good that you confirmed what I was suspecting.

Dec 15, 2010 at 08:26 AM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Dec 15, 2010 at 07:40 AM

avatar image

WilliamD
26.2k 18 34 48

This code is just sample. All I want to know is how to continue the cursor if one row errors and is rolledback. Thanks

Dec 15, 2010 at 07:45 AM David 2 1

As stated, as far as I know you will need to do that in procedural code to catch the exception/error.

Move to sql 2005 and you have the option of try/catch.

The cursor approach remains a poor one based on your example code.

Dec 15, 2010 at 07:52 AM WilliamD

Procedural and moving this database to 2005 isn't an option here but thanks anyway.

Dec 15, 2010 at 07:57 AM David 2 1

Then it looks like you are stuck - anyone else have an answer?

Dec 15, 2010 at 07:58 AM WilliamD

@WilliamD While I was going on and on about why does he RETURN after the rollback, Magnus already answered the question :)

Dec 15, 2010 at 08:18 AM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1069
x501
x73
x25

asked: Dec 15, 2010 at 07:32 AM

Seen: 10875 times

Last Updated: Dec 15, 2010 at 07:32 AM

Copyright 2016 Redgate Software. Privacy Policy