[アーティクル]
04/01/2012

CLR ストアドプロシージャ

ストアドプロシージャはスカラ式では使用できないルーチンです。ストアドプロシージャはスカラ関数とは異なり、表形式の結果やメッセージをクライアントに返す操作、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

sum は int SQL Server データ型として宣言され、CLR ストアドプロシージャに定義された value パラメータは SqlInt32 CLR データ型として指定されます。呼び出し側のプログラムが CLR ストアドプロシージャを実行すると、SQL Server では、SqlInt32 CLR データ型が intSQL 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

注
SQL Server 2005 以降、互換性レベルが "80" の SQL Server データベースでは、マネージコードでユーザー定義型、ストアドプロシージャ、関数、集計、またはトリガを作成することはできません。SQL Server のこれらの CLR 統合機能を使用するには、sp_dbcmptlevel ストアドプロシージャを使用して、データベースの互換性レベルを "100" に設定する必要があります。

SQL Server 2005 以降、互換性レベルが "80" の SQL Server データベースでは、マネージコードでユーザー定義型、ストアドプロシージャ、関数、集計、またはトリガを作成することはできません。SQL Server のこれらの CLR 統合機能を使用するには、sp_dbcmptlevel ストアドプロシージャを使用して、データベースの互換性レベルを "100" に設定する必要があります。

CLR ストアドプロシージャ

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

テーブル値パラメータ

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

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

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

メッセージを返す

表形式の結果を返す

関連項目

概念

その他の技術情報

その他のリソース

CLR ストアド プロシージャ

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

テーブル値パラメータ

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

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

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

メッセージを返す

表形式の結果を返す

関連項目

概念

その他の技術情報

その他のリソース

CLR ストアドプロシージャ

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

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

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