SQL Server:存储过程事务 [英] SQL Server : stored procedure transaction

问题描述

您好，我有一些存储过程可以在我的网站上创建产品和其他内容.现在我必须在事务中运行其中的一些.这是可能的还是我必须只为事务创建一个存储过程?

Hello I got some stored procedures to create products and other stuff on my site. Now I have to run some of them in a transaction. Is that possible or do I have to make a stored procedure only for the transaction?

我能说点什么吗

BEGIN TRAN
"1. stored procedure"
"2. stored procedure"
COMMIT

推荐答案

要添加到上面的其他答案，您可能需要添加一些错误处理:

To add to the other answers above, you may want to add some error handling:

BEGIN TRAN

BEGIN TRY

   EXEC P1

   EXEC P2

   COMMIT TRAN

END TRY
BEGIN CATCH

  ROLLBACK TRAN

END CATCH

<小时>

使用 C# 代码进行更新(我个人发现将转码保留在 sproc 和数据层之外要容易得多 - 使以后编写存储过程更容易):

---

Update with C# code (I personally find it a lot easier to keep trans code out of the sprocs and in the data layer - makes composing stored procedures easier at a later stage):

using (var conn = new SqlConnection(...))

    trans = conn.BeginTransaction();

    try
   {
       ...call P1 using transaction
       ...call P2 using transaction

       trans.Commit();
   }
   catch
   {
       trans.RollBack();
       throw;
   }
}

这篇关于SQL Server:存储过程事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！