SQL 在 BEGIN/END TRANSACTION 中执行存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/180075/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Executing a stored procedure inside BEGIN/END TRANSACTION
提问by Miles
If I create a Stored Procedure in SQL and call it (EXEC spStoredProcedure) within the BEGIN/END TRANSACTION, does this other stored procedure also fall into the transaction?
如果我在 SQL 中创建一个存储过程并EXEC spStoredProcedure在 BEGIN/END TRANSACTION 中调用它 ( )，这个其他存储过程是否也属于事务？
I didn't know if it worked like try/catches in C#.
我不知道它是否像 C# 中的 try/catch 那样工作。
回答by Blorgbeard is out
Yes, everythingthat you do between the Begin Transaction and Commit (or Rollback) is part of the transaction.
是的，您在开始事务和提交（或回滚）之间所做的一切都是事务的一部分。
回答by Miles
Sounds great, thanks a bunch. I ended up doing something like this (because I'm on 05)
听起来不错，非常感谢。我最终做了这样的事情（因为我在 05）
BEGIN TRY
BEGIN TRANSACTION
DO SOMETHING
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
回答by James
As Chris mentioned, you should be careful about rolling the transaction back.
正如克里斯提到的，您应该小心回滚事务。
Specifically this:
具体是这样的：
IF @@TRANCOUNT > 0 ROLLBACK
is not always what you want. You could do something like this
并不总是你想要的。你可以做这样的事情
IF(@@TRANCOUNT = 1) ROLLBACK TRAN
ELSE IF(@@TRANCOUNT > 1) COMMIT TRAN
RETURN @error
This way, the calling proc can inspect the return value from the stored procedure and determine if it wants to commit anyways or continue to bubble up the error.
这样，调用 proc 可以检查存储过程的返回值，并确定它是要提交还是继续冒泡错误。
The reason is that 'COMMIT' will just decrement your transaction counter. Once it decrements the transaction counter to zero, then an actual commit will occur.
原因是“COMMIT”只会减少您的交易计数器。一旦它将事务计数器递减为零，就会发生实际的提交。
回答by Chris Shaffer
I believe in MS SQL Server the stored procedure execution would happen within the transaction, but be very careful with this. If you have nested transactions (ie, transaction outside of the stored procedure and a different transaction inside the stored procedure), a rollback will affect ALL of the transactions, not just the nearest enclosing transaction.
我相信在 MS SQL Server 中，存储过程的执行会在事务中发生，但要非常小心。如果您有嵌套事务（即，存储过程外的事务和存储过程内的不同事务），回滚将影响所有事务，而不仅仅是最近的封闭事务。
回答by kristof
As Chrisand Jamesmentioned, you need to be careful when dealing with nested transactions. There is a set a very good articles on the subject of transactions written by Don Petersonon SQL Server Central , I would recommend having a read of those:
正如Chris和James提到的，在处理嵌套事务时需要小心。Don Peterson在SQL Server Central上写了一组关于事务主题的非常好的文章，我建议您阅读这些文章：
Here there are:
这里有：
