问题描述
我是 T-SQL 的新手，我在处理一些包含事务、游标和存储过程的大型脚本方面遇到了麻烦.所以，我的代码是这样的(这段代码只是我的脚本结构的一个例子，实际上我在 OuterProc 游标中有多个过程，在 InnerProc 游标中有多个操作):
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.
我的代码有什么问题?如果innerProc 内部出现问题，我希望该外部游标的所有操作都回滚并停止内部游标.如果outerProc 出现问题，我希望该游标的所有操作都回滚，但我希望该游标继续循环......
有更好的方法吗?
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?
更新:
在纠正@Bernd Linde 检测到的一些错误后，我在 InnerProc 中添加了一个 try-catch 并将该事务命名为 InnerProc.现在我有这个代码:
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.
InnerProc 必须只有 COMMIT，OuterProc 将负责回滚.为此，当 InnerProc 导致一些必须在 OuterProc 中捕获并被迫像异常一样的错误时.我想如何在 OuterProc 中继续循环，该过程必须有一个 try-catch，其中强制循环并完成回滚.
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.
为了更好地控制交易数量，我使用了@@TRANCOUNT.
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
这篇关于回滚游标内和事务内的事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！