CLR ストアドプロシージャ

[アーティクル]
05/07/2013

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

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

CLR (共通言語ランタイム) では、ストアドプロシージャは Microsoft .NET Framework アセンブリ内のクラスの public static メソッドとして実装されます。この静的メソッドは、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 を使用するとパフォーマンスが向上する可能性もあります。また、サーバーへのラウンドトリップを減らすのにも役立ちます。スカラーパラメーターのリストを使用するなどしてサーバーに複数の要求を送信する代わりに、データを TVP としてサーバーに送信できます。 SQL Server のプロセスで実行されているマネージストアドプロシージャやマネージ関数にユーザー定義のテーブル型をテーブル値パラメーターとして渡したり、戻り値として受け取ったりすることはできません。 TVP の詳細については、「テーブル値パラメーターの使用 (データベースエンジン)」を参照してください。

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

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

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

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

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( <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 パラメーターとして sum を指定します。

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 は int SQL Server データ型として宣言され、CLR ストアドプロシージャに定義された value パラメーターは SqlInt32 CLR データ型として指定されます。呼び出し側のプログラムが CLR ストアドプロシージャを実行すると、SQL Server では、SqlInt32 CLR データ型が int SQL Server データ型に自動的に変換されます。変換できる CLR データ型と変換できない CLR データ型の詳細については、「CLR パラメーターデータのマッピング」を参照してください。

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

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

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

メッセージを返す

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

表形式の結果を返す

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

[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 のカスタム実装を使用して事前処理するには)、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);
      }
   }
}

[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

最初の Send はクライアントにメッセージを送信し、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 以降では、/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 ストアドプロシージャから結果を返す

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

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

メッセージを返す

表形式の結果を返す

関連項目

概念

その他の技術情報

その他のリソース

次の方法で共有

CLR ストアド プロシージャ

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

テーブル値パラメーター

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

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

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

メッセージを返す

表形式の結果を返す

関連項目

概念

その他の技術情報

その他のリソース

CLR ストアドプロシージャ

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

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

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