Close,Deallocate cursor with rollback

Question

Post reply

Close,Deallocate cursor with rollback

Ashwin M N

Hall of Fame

Points: 3668
More actions
November 24, 2010 at 5:28 am

#142640

I have a cursor and transaction within the Procedure.The sample is given below.
Exe:
DECLARE cursor1 CURSOR FOR
SELECT Column1,column2,column3 FROM table1 WHERE ID = @ID
OPEN cursor1
FETCH NEXT FROM cursor1
INTO @Column1,@column2,@column3
WHILE @@FETCH_STATUS = 0
BEGIN
Update @table2 SET Column1 =@Column1,Column2 = @column3 where ID = @column2
FETCH NEXT FROM cursor1
INTO @Column1,@column2,@column3
END
CLOSE cursor1
DEALLOCATE cursor1
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN
END CATCH
When there is failed transaction before close and deallocate cursor it rollbacks the transaction,
when procedure is executed next time will the cursor be closed and deallocated before declare.
When the transaction fails inside the cursor will the cursor be closed and deallocated?
Close cursor on commit enabled is set to false.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

BeginnerBug Hall of Fame Points: 3539 More actions · Answer 1

same doubt i had few days ago... people suggested me to check cursor_status to close and deallocate the cursor..

LutzM SSC Guru Points: 107049 More actions · Answer 2

Why do you use a c.u.r.s.o.r. in the first place?

Wouldn't the following statement do the same without touching the table once for every row?

UPDATE table2

SET Column1 =t1.Column1,Column2 = t2.column3

FROM table1

INNER JOIN table2 ON table1.ID = t2.column2

WHERE t1.ID = @ID

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Ashwin M N Hall of Fame Points: 3668 More actions · Answer 3

The cursor I have shown above is just an example the actual is more complicated than above.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Ashwin M N (11/28/2010)
The cursor I have shown above is just an example the actual is more complicated than above.

Complexity changes nothing here. 99.9999% of the time, there is no need for a cursor or RBAR. Only when you're trying to do some admin to every table or every database is there a reason to resort to either.

Whatever...

Getting back to your problem, I see a COMMIT TRAN but I don't see a BEGIN TRAN anywhere. Where does that come into play in the code you posted?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ashwin M N Hall of Fame Points: 3668 More actions · Answer 5

BEGIN TRY

BEGIN TRAN

DECLARE cursor1 CURSOR FOR

SELECT Column1,column2,column3 FROM table1 WHERE ID = @ID

OPEN cursor1

FETCH NEXT FROM cursor1

INTO @Column1,@column2,@column3

WHILE @@FETCH_STATUS = 0

BEGIN

Update @table2 SET Column1 =@Column1,Column2 = @column3 where ID = @column2

FETCH NEXT FROM cursor1

INTO @Column1,@column2,@column3

END

CLOSE cursor1

DEALLOCATE cursor1

COMMIT TRAN

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0

ROLLBACK TRAN

END CATCH