SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Can we Rollabck whole cursor ?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Vishal_sql
Yak Posting Veteran

88 Posts
Posted - 01/10/2013 :  04:02:00  Show Profile  Reply with Quote
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

India
47173 Posts
Posted - 01/10/2013 :  04:09:00  Show Profile  Reply with Quote
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/
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3328 Posts
Posted - 01/10/2013 :  04:12:15  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

Vishal_sql
Yak Posting Veteran

88 Posts
Posted - 01/10/2013 :  05:22:57  Show Profile  Reply with Quote
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
Go to Top of Page

Vishal_sql
Yak Posting Veteran

88 Posts
Posted - 01/10/2013 :  06:09:59  Show Profile  Reply with Quote
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000