Cursors Rollback? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Cursors Rollback?

Author

Topic

si_g
Starting Member

4 Posts

Posted - 2005-02-03 : 07:16:45

Hi,

Does anyone know if you can perform a rollback on a cursor?
I have a rather large procedure which has a transaction and a few cursors within it.
However, when I do rollback, the cursor data remains updated.

Here is a breakdown of the kind of thing I'm doing:

Begin Trans

- Get Data

- Update Table
- Check for errors

- Begin Cursor 1
- Insert data
- check for errors
- Fetch next
- Close and deallocate cursor 1

- Begin Cursor 2
- Insert data
- check for errors
- Fetch next
- Close and deallocate cursor 2

- If we have errors, rollback trans

else

- commit trans

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-03 : 08:12:42

If you didn't use cursors you wouldn't have that problem

I don't know anything about cursors, other than that they are inefficient. I don't think I've ever actually written one. Sorry I'm not more help, but I really think you should think about losing the cursor(s)

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-03 : 08:34:12

quote:
Originally posted by Seventhnight

If you didn't use cursors you WOULD have that problem

Oh, so he's knackered then

What about deallocating/closing the cursor in the error handler, is that possible?

Andy

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-03 : 08:42:08

quote:
Originally posted by AndyB13

quote:
Originally posted by Seventhnight

If you didn't use cursors you WOULD have that problem

Oh, so he's knackered then

...

Andy

danggit!!

Edited

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

si_g
Starting Member

4 Posts

Posted - 2005-02-03 : 10:02:05

I think cursors are pretty effective in the right places. Sure, they can be a little inefficient but I think they have a bit of an undeserved bad rep.

In my particular case, they are the only option I have. The stored proc I'm creating used to be several procedures called from a VB app but due to issues with some bits running and some bits not we've moved the entire process to this single procedure to stop data inconsistencies from occurring.

Anyway, back on subject....I have tried using a var to count errors then at the end of my proc. I say commit the transaction and close and deallocate the cursors etc.. if all is ok, otherwise, rollback.

This didn't have any effect and it seems the code fired within the cursor doesn't roll back.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-03 : 10:31:50

Well I think the rep is well deserved (I've not seen a good use for a cursor yet

). But I know this has been discussed many times over around SQLTeam.

I read through BOL on cursors, and it kindof led me to believe that the records are committed on closing of the cursor. But it was not very clear about this. I don't know what else to tell you other than to lose the cursors

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-03 : 15:34:43

Without getting into the cursor debate, the answer to your question si_g is Yes. You can rollback transactions that took place in a cursor, even after the cursor is closed and dealocated. The cursor has nothing to do with the ~~transactions that took place within~~. statements that take place within a transaction.


set nocount on
Create Table #Temp
	(RowID int identity(1,1)
	,val int NULL)

GO
insert #temp (val) values (null)
insert #temp (val) values (null)
insert #temp (val) values (null)
insert #temp (val) values (null)
insert #temp (val) values (null)
GO

Select * from #temp
begin tran	
	declare @i int
	Declare crs cursor for
	Select RowID from #temp
	open crs
	Fetch Next from crs into @i
	While @@Fetch_Status = 0
	Begin
		Update #temp Set val = @i where RowID = @i
		Fetch Next from crs into @i
	End	
	close crs
	deallocate crs

Select * from #temp
if @@trancount > 0 rollback tran
Select * from #temp

Be One with the Optimizer
TG

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-02-03 : 15:51:07

>>Without getting into the cursor debate,

Is there a debate to be found when dealing with cursors ???

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-03 : 15:54:47

No debate from here! After writing that last example I had to go take a shower.

Be One with the Optimizer
TG

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-03 : 16:00:10

Cursors and loops----don't forget those loops!

Semper fi,

Xerxes, USMC (Ret)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-02-03 : 16:00:25

>>After writing that last example I had to go take a shower.

Unclean.. Unclean...

I still remember the days as the light began to shine brighter with regards to set-based processing...

Ahhhh... Like being born again :)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-03 : 16:05:21

You said it...However I feel I must confess that I did not need to refer to BOL to write that example.

In fact, dammit, it felt good. You'll see me tomorrow at a meeting saying, "Hello, my name is TG, I've been loop-less 1 day".

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources