JavaBoy060299
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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.
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.
ASKER
i need to process a batch of data
and i want to maintain transaction because some of the operations involve delete and insert statement.
and i want to maintain transaction because some of the operations involve delete and insert statement.
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.
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.
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
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
ASKER
i need to loop through the cursor and maintain transaction for each iteration