CLR ストアドプロシージャ

[アーティクル]
04/27/2017

ストアドプロシージャは、スカラー式では使用できないルーチンです。ストアドプロシージャはスカラー関数とは異なり、表形式の結果やメッセージをクライアントに返す操作、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# と同じ方法で出力パラメーターがサポートされないことに注意してください。次のように、パラメーターを参照で指定し、OUT()> 属性を適用<して OUTPUT パラメーターを表す必要があります。

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

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

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

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

次に示すのは、SqlPipe.Send を送信したり、単純にテキスト文字列を送信するために使用する、SqlDataReader メソッドのオーバーロードです。

メッセージを返す

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

表形式の結果を返す

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

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 のカスタム実装を使用して事前処理するには)、Send メソッドの SqlDataReader を受け取るオーバーロードを使用します。このメソッドは、前述の直接的なメソッドより少し動作が遅くなりますが、クライアントにデータを送信する前に、きわめて柔軟にデータを操作できます。

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;

Note

メッセージと結果セットはクライアントアプリケーションで個別に取得されます。たとえば、結果セット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

Note

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 ユーザー定義関数
 CLR ユーザー定義型
 CLR トリガー

次の方法で共有

CLR ストアドプロシージャ

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

テーブル値パラメーター

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

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

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

メッセージを返す

表形式の結果を返す

参照

その他のリソース

次の方法で共有

CLR ストアド プロシージャ

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

テーブル値パラメーター

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

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

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

メッセージを返す

表形式の結果を返す

参照

その他のリソース

CLR ストアドプロシージャ

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

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

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