現在のトランザクションへのアクセス

次の例では、Transaction.Rollback メソッドを使用してマネージ プロシージャからトランザクションをロールバックします。マネージ コード内にある Transaction.Rollback メソッドの前後の try/catch ブロックに注意してください。Transact-SQL スクリプトは、アセンブリおよびマネージ ストアド プロシージャを作成します。マネージ プロシージャが実行を終了したときにスローされる例外をキャッチするため、EXEC uspRollbackFromProc ステートメントが try/catch ブロックにラップされていることに注意してください。

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Transactions;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void uspRollbackFromProc()
{
   using (SqlConnection connection = new SqlConnection(@"context connection=true"))
   {
      // Open the connection.
      connection.Open();

      bool successCondition = true;

      // Success condition is met.
      if (successCondition)
      {
         SqlContext.Pipe.Send("Success condition met in procedure."); 
         // Perform other actions here.
      }
            
      //  Success condition is not met, the transaction will be rolled back.
      else
      {
         SqlContext.Pipe.Send("Success condition not met in managed procedure. Transaction rolling back...");
         try
         {
               // Get the current transaction and roll it back.
               Transaction trans = Transaction.Current;
               trans.Rollback();
         }
         catch (SqlException ex)
         {
            // Catch the expected exception. 
            // This allows the connection to close correctly.                    
         }  
      }

      // Close the connection.
      connection.Close();
   }
}
};

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Transactions

Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub  uspRollbackFromProc ()
   Using connection As New SqlConnection("context connection=true")

   ' Open the connection.
   connection.Open()

   Dim successCondition As Boolean
   successCondition = False

   ' Success condition is met.
   If successCondition Then

      SqlContext.Pipe.Send("Success condition met in procedure.")

      ' Success condition is not met, the transaction will be rolled back.

   Else
      SqlContext.Pipe.Send("Success condition not met in managed procedure. Transaction rolling back...")
      Try
         ' Get the current transaction and roll it back.
         Dim trans As Transaction
         trans = Transaction.Current
         trans.Rollback()

      Catch ex As SqlException
         ' Catch the exception instead of throwing it.  
         ' This allows the connection to close correctly.                    
      End Try

   End If

   ' Close the connection.
   connection.Close()

End Using
End Sub
End Class

Transact-SQL

--Register assembly.
CREATE ASSEMBLY TestProcs FROM 'C:\Programming\TestProcs.dll' 
Go
CREATE PROCEDURE uspRollbackFromProc AS EXTERNAL NAME TestProcs.StoredProcedures.uspRollbackFromProc
Go

-- Execute procedure.
BEGIN TRY
BEGIN TRANSACTION 
-- Perform other actions.
Exec uspRollbackFromProc
-- Perform other actions.
PRINT N'Commiting transaction...'
COMMIT TRANSACTION
END TRY

BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNum, ERROR_MESSAGE() AS ErrorMessage
PRINT N'Exception thrown, rolling back transaction.'
ROLLBACK TRANSACTION
PRINT N'Transaction rolled back.' 
END CATCH
Go

-- Clean up.
DROP Procedure uspRollbackFromProc;
Go
DROP ASSEMBLY TestProcs;
Go