CLR ストアドプロシージャ

[アーティクル]
03/29/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 の詳細については、次を参照してください。テーブル値パラメーター (データベースエンジン)します。

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

.NET Framework ストアドプロシージャからの情報はいくつかの形式で返すことができます。出力パラメーター、表形式の結果、およびメッセージの形式を使用できます。

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

Transact-SQL ストアドプロシージャと同様に、OUTPUT パラメーターを使用して .NET Framework ストアドプロシージャから情報を返すことができます。 Transact-SQL ストアドプロシージャの作成に使用する .NET Framework DML 構文は、Transact-SQL で記述されたストアドプロシージャの作成に使用する構文と同じです。 .NET Framework クラスの実装コードの対応するパラメーターは、引数として参照渡しのパラメーターを使用する必要があります。 Visual Basic は出力パラメーターを Visual C# と同様にはサポートしていません。参照渡しでパラメーターを指定する必要があります。

Imports System.Runtime.InteropServices  
…  
Public Shared Sub PriceSum (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);  
            }  
         }           
      }  
   }  
}

Visual Basic

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( 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、データベースでプロシージャを作成するために使用し、指定合計出力パラメーターとして。

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

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

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

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

これらの複数のオーバーロードは、 SqlPipe.Sendなどを送信する&1; つの方法、 SqlDataReaderと、単にテキスト文字列を送信します。

メッセージを返す

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

表形式の結果を返す

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

[C#]

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);  
      }  
   }  
}

[Visual Basic]

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します。このメソッドは前半で説明した直接的なメソッドよりもわずかに時間がかかりますが、このメソッドを使用すると、クライアントにデータを送信する前にそのデータを非常に柔軟に操作できます。

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);  
      }  
   }  
}

[Visual Basic]

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);  
   }  
}

[Visual Basic]

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);  
      }  
   }  
}

[Visual Basic]

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

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

これらの例は、説明のみの目的でここに記載しています。計算を集中的に行うアプリケーションには、実際には単純な Transact-SQL ステートメントよりも CLR 関数の方が適しています。上の例とほぼ同等の 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、Visual C のデータベースオブジェクト (ストアドプロシージャなど) でコンパイルされた/clr: 純粋な実行はサポートされていません。

生成されるアセンブリは登録でき、次の 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 ストアドプロシージャ