CLRのストアドプロシージャ

2017-04-27

ストアドプロシージャは、スカラー式では使用できないルーチンです。スカラー関数とは異なり、表形式の結果とメッセージをクライアントに返し、データ定義言語 (DDL) ステートメントとデータ操作言語 (DML) ステートメントを呼び出し、出力パラメーターを返すことができます。 CLR 統合の利点と、マネージドコードと Transact-SQL の選択については、「 CLR 統合の概要」を参照してください。

CLR ストアドプロシージャの要件

共通言語ランタイム (CLR) では、ストアドプロシージャは、Microsoft.NET Framework アセンブリ内のクラスのパブリック静的メソッドとして実装されます。静的メソッドは、void として宣言するか、整数値を返すことができます。整数値を返す場合、返される整数はプロシージャからのリターンコードとして扱われます。例えば次が挙げられます。

EXECUTE @return_status = procedure_name

@return_status変数には、メソッドによって返される値が含まれます。メソッドが void と宣言されている場合、戻りコードは 0 になります。

メソッドがパラメーターを受け取る場合、.NET Framework 実装内のパラメーターの数は、ストアドプロシージャの Transact-SQL 宣言で使用されるパラメーターの数と同じである必要があります。

CLR ストアドプロシージャに渡されるパラメーターには、マネージドコードで同等のネイティブ SQL Server 型を指定できます。プロシージャを作成する Transact-SQL 構文では、これらの型を、同等のネイティブ SQL Server 型で指定する必要があります。型変換の詳細については、「 CLR パラメーターデータのマッピング」を参照してください。

テーブル値パラメーター

テーブル値パラメーター (TVP) とは、プロシージャや関数に渡されるユーザー定義のテーブル型です。TVP を使用すると、複数行のデータを効率的にサーバーに渡すことができます。 TVP はパラメーター配列と同様の機能を提供しますが、Transact-SQL との柔軟性が向上し、より緊密に統合できます。テーブル値パラメーターを使用するとパフォーマンスが向上する可能性もあります。また、サーバーへのラウンドトリップを減らすのにも役立ちます。スカラーパラメーターのリストを使用するなどしてサーバーに複数の要求を送信する代わりに、データを TVP としてサーバーに送信できます。ユーザー定義テーブル型は、SQL Server プロセスで実行されているマネージドストアドプロシージャまたは関数に対して、テーブル値パラメーターとして渡したり、そのパラメーターから返したりすることはできません。 TVP の詳細については、「 Table-Valued パラメーターの使用 (データベースエンジン)」を参照してください。

CLR ストアドプロシージャから結果を返す

情報は、いくつかの方法で .NET Framework ストアドプロシージャから返される場合があります。これには、出力パラメーター、表形式の結果、メッセージが含まれます。

OUTPUT パラメーターと CLR ストアドプロシージャ

Transact-SQL ストアドプロシージャと同様に、OUTPUT パラメーターを使用して .NET Framework ストアドプロシージャから情報を返すこともできます。 .NET Framework ストアドプロシージャの作成に使用される Transact-SQL DML 構文は、Transact-SQL で記述されたストアドプロシージャの作成に使用される構文と同じです。 .NET Framework クラスの実装コードの対応するパラメーターでは、引数として参照渡しパラメーターを使用する必要があります。 Visual Basic では、C# と同じ方法で出力パラメーターがサポートされないことに注意してください。次のように、パラメーターを参照で指定し、OUTPUT パラメーターを表すために <Out()> 属性を適用する必要があります。

Imports System.Runtime.InteropServices  
...  
Public Shared Sub PriceSum ( <Out()> ByRef value As SqlInt32)

OUTPUT パラメーターを使用して情報を返すストアドプロシージャを次に示します。

using System;  
using System.Data.SqlTypes;  
using System.Data.SqlClient;  
using Microsoft.SqlServer.Server;   
  
public class StoredProcedures   
{  
   [Microsoft.SqlServer.Server.SqlProcedure]  
   public static void PriceSum(out SqlInt32 value)  
   {  
      using(SqlConnection connection = new SqlConnection("context connection=true"))   
      {  
         value = 0;  
         connection.Open();  
         SqlCommand command = new SqlCommand("SELECT Price FROM Products", connection);  
         SqlDataReader reader = command.ExecuteReader();  
  
         using (reader)  
         {  
            while( reader.Read() )  
            {  
               value += reader.GetSqlInt32(0);  
            }  
         }           
      }  
   }  
}

Imports System  
Imports System.Data  
Imports System.Data.Sql  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
Imports System.Runtime.InteropServices  
  
'The Partial modifier is only required on one class definition per project.  
Partial Public Class StoredProcedures   
    ''' <summary>  
    ''' Executes a query and iterates over the results to perform a summation.  
    ''' </summary>  
    <Microsoft.SqlServer.Server.SqlProcedure> _  
    Public Shared Sub PriceSum( <Out()> ByRef value As SqlInt32)  
  
        Using connection As New SqlConnection("context connection=true")  
           value = 0  
           Connection.Open()  
           Dim command As New SqlCommand("SELECT Price FROM Products", connection)  
           Dim reader As SqlDataReader  
           reader = command.ExecuteReader()  
  
           Using reader  
              While reader.Read()  
                 value += reader.GetSqlInt32(0)  
              End While  
           End Using  
        End Using          
    End Sub  
End Class

上記の CLR ストアドプロシージャを含むアセンブリがサーバー上にビルドおよび作成されると、次の Transact-SQL を使用してデータベースにプロシージャを作成し、合計を OUTPUT パラメーターとして指定します。

CREATE PROCEDURE PriceSum (@sum int OUTPUT)  
AS EXTERNAL NAME TestStoredProc.StoredProcedures.PriceSum  
-- if StoredProcedures class was inside a namespace, called MyNS,  
-- you would use:  
-- AS EXTERNAL NAME TestStoredProc.[MyNS.StoredProcedures].PriceSum

合計は SQL Server データ型として宣言されていること、および CLR ストアドプロシージャで定義されている値パラメーターが CLR データ型として指定されていることに注意してください。呼び出し元のプログラムが CLR ストアドプロシージャを実行すると、SQL Server は SqlInt32 CLR データ型を intSQL Server データ型に自動的に変換します。変換できる CLR データ型と変換できない CLR データ型の詳細については、「 CLR パラメーターデータのマッピング」を参照してください。

表形式の結果とメッセージを返す

表形式の結果とメッセージをクライアントに返すには、SqlPipe オブジェクトを使用します。このオブジェクトは、SqlContext クラスの Pipe プロパティを使用して取得されます。 SqlPipe オブジェクトには、Send メソッドがあります。 Send メソッドを呼び出すことで、パイプを介して呼び出し元のアプリケーションにデータを送信できます。

これらは、 SqlPipe.Send メソッドのいくつかのオーバーロードです。たとえば、 SqlDataReader を送信するオーバーロードと、単にテキスト文字列を送信するオーバーロードなどです。

メッセージを返す

SqlPipe.Send(string)を使用して、クライアントアプリケーションにメッセージを送信します。メッセージのテキストは 8,000 文字に制限されています。メッセージが 8,000 文字を超えると、メッセージは切り捨てられます。

表形式の結果を返す

クエリの結果をクライアントに直接送信するには、SqlPipe オブジェクトの Execute メソッドのいずれかのオーバーロードを使用します。これは、データがマネージドメモリにコピーされずにネットワークバッファーに転送されるため、クライアントに結果を返す最も効率的な方法です。例えば次が挙げられます。

using System;  
using System.Data;  
using System.Data.SqlTypes;  
using System.Data.SqlClient;  
using Microsoft.SqlServer.Server;   
  
public class StoredProcedures   
{  
   /// <summary>  
   /// Execute a command and send the results to the client directly.  
   /// </summary>  
   [Microsoft.SqlServer.Server.SqlProcedure]  
   public static void ExecuteToClient()  
   {  
   using(SqlConnection connection = new SqlConnection("context connection=true"))   
   {  
      connection.Open();  
      SqlCommand command = new SqlCommand("select @@version", connection);  
      SqlContext.Pipe.ExecuteAndSend(command);  
      }  
   }  
}

Imports System  
Imports System.Data  
Imports System.Data.Sql  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
  
'The Partial modifier is only required on one class definition per project.  
Partial Public Class StoredProcedures   
    ''' <summary>  
    ''' Execute a command and send the results to the client directly.  
    ''' </summary>  
    <Microsoft.SqlServer.Server.SqlProcedure> _  
    Public Shared Sub ExecuteToClient()  
        Using connection As New SqlConnection("context connection=true")  
            connection.Open()  
            Dim command As New SqlCommand("SELECT @@VERSION", connection)  
            SqlContext.Pipe.ExecuteAndSend(command)  
        End Using  
    End Sub  
End Class

インプロセスプロバイダーを介して以前に実行されたクエリの結果を送信する (または、SqlDataReaderのカスタム実装を使用してデータを前処理する) には、SqlDataReaderを受け取るSend メソッドのオーバーロードを使用します。このメソッドは、前に説明したダイレクトメソッドよりも少し遅くなりますが、クライアントに送信される前にデータを操作する柔軟性が高くなります。

using System;  
using System.Data;  
using System.Data.SqlTypes;  
using System.Data.SqlClient;  
using Microsoft.SqlServer.Server;   
  
public class StoredProcedures   
{  
   /// <summary>  
   /// Execute a command and send the resulting reader to the client  
   /// </summary>  
   [Microsoft.SqlServer.Server.SqlProcedure]  
   public static void SendReaderToClient()  
   {  
      using(SqlConnection connection = new SqlConnection("context connection=true"))   
      {  
         connection.Open();  
         SqlCommand command = new SqlCommand("select @@version", connection);  
         SqlDataReader r = command.ExecuteReader();  
         SqlContext.Pipe.Send(r);  
      }  
   }  
}

Imports System  
Imports System.Data  
Imports System.Data.Sql  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
  
'The Partial modifier is only required on one class definition per project.  
Partial Public Class StoredProcedures   
    ''' <summary>  
    ''' Execute a command and send the results to the client directly.  
    ''' </summary>  
    <Microsoft.SqlServer.Server.SqlProcedure> _  
    Public Shared Sub SendReaderToClient()  
        Using connection As New SqlConnection("context connection=true")  
            connection.Open()  
            Dim command As New SqlCommand("SELECT @@VERSION", connection)  
            Dim reader As SqlDataReader  
            reader = command.ExecuteReader()  
            SqlContext.Pipe.Send(reader)  
        End Using  
    End Sub  
End Class

動的な結果セットを作成し、それを設定してクライアントに送信するには、現在の接続からレコードを作成し、 SqlPipe.Sendを使用して送信できます。

using System.Data;  
using System.Data.SqlClient;  
using Microsoft.SqlServer.Server;   
using System.Data.SqlTypes;  
  
public class StoredProcedures   
{  
   /// <summary>  
   /// Create a result set on the fly and send it to the client.  
   /// </summary>  
   [Microsoft.SqlServer.Server.SqlProcedure]  
   public static void SendTransientResultSet()  
   {  
      // Create a record object that represents an individual row, including it's metadata.  
      SqlDataRecord record = new SqlDataRecord(new SqlMetaData("stringcol", SqlDbType.NVarChar, 128));  
  
      // Populate the record.  
      record.SetSqlString(0, "Hello World!");  
  
      // Send the record to the client.  
      SqlContext.Pipe.Send(record);  
   }  
}

Imports System  
Imports System.Data  
Imports System.Data.Sql  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
  
'The Partial modifier is only required on one class definition per project.  
Partial Public Class StoredProcedures   
    ''' <summary>  
    ''' Create a result set on the fly and send it to the client.  
    ''' </summary>  
    <Microsoft.SqlServer.Server.SqlProcedure> _  
    Public Shared Sub SendTransientResultSet()  
        ' Create a record object that represents an individual row, including it's metadata.  
        Dim record As New SqlDataRecord(New SqlMetaData("stringcol", SqlDbType.NVarChar, 128) )  
  
        ' Populate the record.  
        record.SetSqlString(0, "Hello World!")  
  
        ' Send the record to the client.  
        SqlContext.Pipe.Send(record)          
    End Sub  
End Class

表形式の結果とメッセージを SqlPipe 経由で送信する例を次に示します。

using System.Data.SqlClient;  
using Microsoft.SqlServer.Server;   
  
public class StoredProcedures   
{  
   [Microsoft.SqlServer.Server.SqlProcedure]  
   public static void HelloWorld()  
   {  
      SqlContext.Pipe.Send("Hello world! It's now " + System.DateTime.Now.ToString()+"\n");  
      using(SqlConnection connection = new SqlConnection("context connection=true"))   
      {  
         connection.Open();  
         SqlCommand command = new SqlCommand("SELECT ProductNumber FROM ProductMaster", connection);  
         SqlDataReader reader = command.ExecuteReader();  
         SqlContext.Pipe.Send(reader);  
      }  
   }  
}

Imports System  
Imports System.Data  
Imports System.Data.Sql  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
  
'The Partial modifier is only required on one class definition per project.  
Partial Public Class StoredProcedures   
    ''' <summary>  
    ''' Execute a command and send the results to the client directly.  
    ''' </summary>  
    <Microsoft.SqlServer.Server.SqlProcedure> _  
    Public Shared Sub HelloWorld()  
        SqlContext.Pipe.Send("Hello world! It's now " & System.DateTime.Now.ToString() & "\n")  
        Using connection As New SqlConnection("context connection=true")  
            connection.Open()  
            Dim command As New SqlCommand("SELECT ProductNumber FROM ProductMaster", connection)  
            Dim reader As SqlDataReader  
            reader = command.ExecuteReader()  
            SqlContext.Pipe.Send(reader)  
        End Using  
    End Sub  
End Class

最初の Send はクライアントにメッセージを送信し、2 つ目の SqlDataReaderを使用して表形式の結果を送信します。

これらの例は説明のみを目的としています。 CLR 関数は、計算負荷の高いアプリケーションの単純な Transact-SQL ステートメントよりも適しています。前の例とほぼ同等 Transact-SQL ストアドプロシージャは次のとおりです。

CREATE PROCEDURE HelloWorld() AS  
BEGIN  
PRINT('Hello world!')  
SELECT ProductNumber FROM ProductMaster  
END;

注

メッセージと結果セットは、クライアントアプリケーションで異なる方法で取得されます。たとえば、SQL Server Management Studio の結果セットが結果ビューに表示され、[メッセージ] ウィンドウに メッセージ が表示されます。

上記の Visual C# コードがファイル MyFirstUdp.csに保存され、次のようにコンパイルされる場合:

csc /t:library /out:MyFirstUdp.dll MyFirstUdp.cs

または、上記の Visual Basic コードがファイル MyFirstUdp.vbに保存され、次のようにコンパイルされている場合は、次のようになります。

vbc /t:library /out:MyFirstUdp.dll MyFirstUdp.vb

注

SQL Server 2005 以降では、 /clr:pure でコンパイルされた Visual C++ データベースオブジェクト (ストアドプロシージャなど) は、実行ではサポートされていません。

結果のアセンブリを登録し、次の DDL を使用してエントリポイントを呼び出すことができます。

CREATE ASSEMBLY MyFirstUdp FROM 'C:\Programming\MyFirstUdp.dll';  
CREATE PROCEDURE HelloWorld  
AS EXTERNAL NAME MyFirstUdp.StoredProcedures.HelloWorld;  
EXEC HelloWorld;

こちらもご覧ください

CLR User-Defined 関数
 CLR User-Defined 型
 CLR トリガー

次の方法で共有

CLRのストアドプロシージャ

CLR ストアド プロシージャの要件