I'm pretty new in T-SQL and I'm in trouble with some huge scripts with transactions, cursors and storage procedures. So, my code is something like this (this code is just an example of the structure of my scripts, in fact I have multiples procedures inside OuterProc cursor and multiple operations inside InnerProc cursor):
create proc InnerProc
as
begin
declare @Id int
begin tran
declare mycursor cursor local static read_only forward_only
for select Id
from MyOtherTable
open mycursor
fetch next from mycursor into @Id
while @@fetch_status = 0
begin
select 1/0
if @@ERROR <> 0
begin
rollback tran
return @@ERROR
end
fetch next from mycursor into @Id
end
close mycursor
deallocate mycursor
commit tran
end
create proc OuterProc
as
begin
declare @Id int
begin tran
declare mycursor cursor local static read_only forward_only
for select Id
from MyTable
open mycursor
fetch next from mycursor into @Id
while @@fetch_status = 0
begin
exec @error = InnerProc
if @@ERROR <> 0
begin
rollback tran
return
end
else
commit tran
fetch next from mycursor into @Id
end
close mycursor
deallocate mycursor
end
With this structure I have this error:
Msg 515, Level 16, State 2, Procedure InnerProc, Line 448
Cannot insert the value NULL into column 'InitialQuantity', table 'MySecondTable'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Msg 266, Level 16, State 2, Procedure InnerProc, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
Msg 3903, Level 16, State 1, Procedure CreateSASEExtraction, Line 79
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
What is wrong with my code? If something goes wrong inside innerProc, I want all operations for that outer cursor rollback and stop the inner cursor. If something goes wrong in the outerProc I want all operations for that cursor to rollback but I want that cursor continue to looping...
There is a better way to do this?
UPDATE:
After I correct some errors @Bernd Linde detected, I add a try-catch in InnerProc and I named the InnerProc transaction. Now I have this code:
create proc InnerProc
as
begin
declare @Id int
begin tran
begin try
declare mycursor cursor local static read_only forward_only
for select Id
from MyOtherTable
open mycursor
fetch next from mycursor into @Id
while @@fetch_status = 0
begin
select 1/0
if @@ERROR <> 0
return @@ERROR
fetch next from mycursor into @Id
end
close mycursor
deallocate mycursor
commit tran
return 0
end try
begin catch
return @@ERROR
end catch
end
create proc OuterProc
as
begin
declare @Id int
declare mycursor cursor local static read_only forward_only
for select Id
from MyTable
open mycursor
fetch next from mycursor into @Id
while @@fetch_status = 0
begin
begin tran
exec @error = InnerProc
if @@ERROR <> 0
begin
rollback tran
return
end
else
commit tran
fetch next from mycursor into @Id
end
close mycursor
deallocate mycursor
end
But now I have other error message:
Msg 266, Level 16, State 2, Procedure InnerProc, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 2.
How can I solve this?
After many attemps, finally I get it.
The InnerProc must only have COMMITs and the OuterProc will be responsible for rollback. For that, when InnerProc causes some error that must be catch in the OuterProc and forced to act like a exception. How I want to continue looping in the OuterProc, that procedure must have a try-catch where the looping is forced and the rollback is done.
For a better transaction number control I used the @@TRANCOUNT.
So I solve the problem with this code:
create proc InnerProc
as
begin
declare @Id int
begin try
begin tran
declare mycursor cursor local static read_only forward_only
for select Id
from MyOtherTable
open mycursor
fetch next from mycursor into @Id
while @@fetch_status = 0
begin
select 1/0
IF @@ERROR <> 0
begin
if @@TRANCOUNT > 0
rollback tran
close mycursor
deallocate mycursor
return @@ERROR
end
fetch next from mycursor into @Id
end
close mycursor
deallocate mycursor
commit tran
return 0
end try
begin catch
close mycursor
deallocate mycursor
return @@ERROR
end catch
end
create proc OuterProc
as
begin
declare @Id int
declare mycursor cursor local static read_only forward_only
for select Id
from MyTable
open mycursor
fetch next from mycursor into @Id
while @@fetch_status = 0
begin
begin tran
begin try
exec @error = InnerProc
if @@ERROR <> 0
RAISERROR('Exception',1,1)
if@@TRANCOUNT > 0
commit tran
fetch next from mycursor into @Id, @Name, @CodeDGAE, @Code, @NUIT, @Project
end try
begin catch
if @@TRANCOUNT > 0
rollback tran
fetch next from mycursor into @Id, @Name, @CodeDGAE, @Code, @NUIT, @Project
end catch
end
close mycursor
deallocate mycursor
end