Can we Rollabck whole cursor ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Can we Rollabck whole cursor ?

Author

Topic

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2013-01-10 : 04:02:00

I dont know my question is correct or not.

I have a stored procedure which uses Cursor for inserting data in Table (production).

Cursor is used because every time new values are assigned for few columns.

and then inserting is done into Table (production) in each loop.

Now Suppose if there are 10 rows and in 9th row there is error,

so cursor will insert 8 records in Table (production).

How can we rollback all rows in inserted Table (production) in or after Cursor.

I am trying to use Begin Tran ,rollback tran so that if any error occurs in cursor complete data should be rollback

Is it possible in Cursor or after cursor?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-10 : 04:09:00

if you want to rollback entire cursor operation it needs to be wrapped inside a begin tran..end tran block

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-10 : 04:12:15

begin try
begin tran
do cursor processing
commit tran
end try
begin tran
begin catch
rollback tran
raiserror ('failed',16,-1)
end catch

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2013-01-10 : 05:22:57

Thanks visakh and Nigel.

Nigel I beleive u added one more BEGIN TRAN after END TRY.see if m not wrong.

quote:
begin try
begin tran
do cursor processing
commit tran
end try
begin tran
begin catch
rollback tran
raiserror ('failed',16,-1)
end catch

begin try
begin tran
cursor processing
commit tran
end try
begin catch
rollback tran
raiserror ('failed',16,-1)
end catch

Thanks again

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2013-01-10 : 06:09:59

I have one doubt about using begin try end try here.

If there is error in any record say 8th row in cursor so as BEGIN TRY and BEGIN CATCH is used , it will go to catch block and the CLOSE Cursor and dealloacte cursor in BEGIN TRY doesnt gets executed ?

so if next time the procedure is executed will it give the cursor already exists?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources