如何确保嵌套事务彼此独立提交？
如果我有一个存储过程使用不同的参数多次执行另一个存储过程，是否可以让每个调用独立于其他调用提交？
换句话说，如果嵌套过程的前两次执行成功，但第三次执行失败，是否可以保留前两次执行的结果（而不是回滚它们）？
我在 SQL Server 2000 中定义了一个类似这样的存储过程：
CREATE PROCEDURE toplevel_proc ..
AS
BEGIN
...
while @row_count <= @max_rows
begin
select @parameter ... where rownum = @row_count
exec nested_proc @parameter
select @row_count = @row_count + 1
end
END
If I have a stored procedure that executes another stored procedure several times with different arguments, is it possible to have each of these calls commit independently of the others?
In other words, if the first two executions of the nested procedure succeed, but the third one fails, is it possible to preserve the results of the first two executions (and not roll them back)?
I have a stored procedure defined something like this in SQL Server 2000:
CREATE PROCEDURE toplevel_proc ..
AS
BEGIN
...
while @row_count <= @max_rows
begin
select @parameter ... where rownum = @row_count
exec nested_proc @parameter
select @row_count = @row_count + 1
end
END
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
首先，SQL Server 中没有嵌套事务这样的东西
但是，您可以使用 SAVEPOINTs 按照此示例（抱歉，太长，无法在此处重现）来自其他 SO 用户 Remus Rusanu
编辑：AlexKuznetsov 提到（尽管他删除了他的答案）如果交易注定失败，这将不起作用。当 SET XACT_ABORT ON 或某些触发错误时，可能会发生这种情况。
First off, there is no such thing as a nested transaction in SQL Server
However, you can use SAVEPOINTs as per this example (too long to reproduce here sorry) from fellow SO user Remus Rusanu
Edit: AlexKuznetsov mentioned (he deleted his answer though) that this won't work if a transaction is doomed. This can happen with SET XACT_ABORT ON or some trigger errors.
来自博尔：
我还从另一个线程这里找到了以下内容：
所以这看起来是可能的。
From BOL:
I also found the following from another thread here:
So it would appear possible.