?
Solved

Using Transaction inside Cursor

Posted on 2003-02-26
8
Medium Priority
?
3,413 Views
Last Modified: 2008-01-16
Hi,

I have stored procedure as follow:

Declare xxx Cursor For Select * from table X
open xxx
Fetch Next from xxx into yyy
While @@Fetch_status = 0
 begin
   if <condition>
      begin
         Begin Tran  
           <statement1>
              if @@ROWCOUNT = 0
                  ROLLBACK Tran  
           <statement2>  
              if @@ROWCOUNT = 0
                  ROLLBAC Tran
              else
                  Commit Tran  
      end
   else
      begin
         <statement 3>
      end
 end

close xxx
dealocate xxx


my question : is it a correct way using transaction with cursor ? do i need RETURN statement?
any advise?
0
Comment
Question by:JavaBoy060299
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 18

Accepted Solution

by:
nigelrivett earned 200 total points
ID: 8030377
Depends on what you want to do.
Either this or change the goto's to kill the cursor and return.
I wouldn't recommend an explicit cursor as they are never necessary and usually a bad idea.

begin
  if <condition>
  begin
     Begin Tran  
       <statement1>
       if @@ROWCOUNT = 0
       begin
          ROLLBACK Tran
          goto nxtrow
       end
       <statement2>  
       if @@ROWCOUNT = 0
       begin
          ROLLBACK Tran
          goto nxtrow
       end
       Commit Tran  
     end
  else
     begin
        <statement 3>
     end
nxtrow:
end
0
 

Author Comment

by:JavaBoy060299
ID: 8030940
no, i don't want to kill the cursor.

i need to loop through the cursor and maintain transaction for each iteration
0
 

Expert Comment

by:Tyip
ID: 8031148
Try this:

declare cur cursor ...
open cursor
while 1 = 1
begin
  fetch next from cur into ...
  if @@fetch_status <> 0
    break

  begin tran
  statement1
  if @@rowcount = 0
  begin
    rollback tran
    continue
  end
  statement2
  if @@rowcount = 0
  begin
    rowback tran
    continue
  end
  commit tran
end
close cur
deallocate cur
0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 18

Expert Comment

by:nigelrivett
ID: 8034209
Then use what I posted.
For each row it will terminate the processing if @@rowcount is 0 and continue with the next row.

Bit strange using rowcount for this.
Assume statements are not sql statements but SPs which do something as the last statement to set the rowcount.
would be better to have a return statement or output parameter.
0
 

Author Comment

by:JavaBoy060299
ID: 8178225
i need to process a batch of data
and i want to maintain transaction because some of the operations involve delete and insert statement.

0
 

Expert Comment

by:CleanupPing
ID: 9276296
JavaBoy:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
LVL 34

Expert Comment

by:arbert
ID: 10910696
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.  

I will leave the following recommendation for this question in the Cleanup topic area:

Accept Answer From nigelrivett

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

Arbert
EE Cleanup Volunteer
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question