Using Transaction inside Cursor

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?
JavaBoy060299Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
nigelrivettConnect With a Mentor Commented:
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
 
JavaBoy060299Author Commented:
no, i don't want to kill the cursor.

i need to loop through the cursor and maintain transaction for each iteration
0
 
TyipCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
nigelrivettCommented:
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
 
JavaBoy060299Author Commented:
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
 
CleanupPingCommented:
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
 
arbertCommented:
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
All Courses

From novice to tech pro — start learning today.