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

David 2 1 gravatar image

David 2 1
412 45 50 52

(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

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.4k 16 19 33

@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

Oleg gravatar image

Oleg
15.9k 2 4 24

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

WilliamD gravatar image

WilliamD
25.9k 17 19 41

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x985
x473
x60
x21

asked: Dec 15, 2010 at 07:32 AM

Seen: 7986 times

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