[解決済み] SQL Serverストアドプロシージャに配列を渡す方法

質問

SQL Serverストアドプロシージャに配列を渡すには？

例えば、従業員のリストがあります。このリストをテーブルとして使用し、別のテーブルと結合したいと思います。しかし、従業員のリストは、C#からパラメータとして渡される必要があります。

どのように解決するのですか？

SQL Server 2008 (またはそれ以降)

まず、データベース内に、以下の2つのオブジェクトを作成します。

CREATE TYPE dbo.IDList
AS TABLE
(
  ID INT
);
GO

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List AS dbo.IDList READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT ID FROM @List; 
END
GO

今度はC#のコードで。

// Obtain your list of ids to send, this is just an example call to a helper utility function
int[] employeeIds = GetEmployeeIds();

DataTable tvp = new DataTable();
tvp.Columns.Add(new DataColumn("ID", typeof(int)));

// populate DataTable from your List here
foreach(var id in employeeIds)
    tvp.Rows.Add(id);

using (conn)
{
    SqlCommand cmd = new SqlCommand("dbo.DoSomethingWithEmployees", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter tvparam = cmd.Parameters.AddWithValue("@List", tvp);
    // these next lines are important to map the C# DataTable object to the correct SQL User Defined Type
    tvparam.SqlDbType = SqlDbType.Structured;
    tvparam.TypeName = "dbo.IDList";
    // execute query, consume results, etc. here
}

SQL Server 2005

SQL Server 2005 を使用している場合、やはり XML よりも分割関数をお勧めします。まず、関数を作成します。

CREATE FUNCTION dbo.SplitInts
(
   @List      VARCHAR(MAX),
   @Delimiter VARCHAR(255)
)
RETURNS TABLE
AS
  RETURN ( SELECT Item = CONVERT(INT, Item) FROM
      ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')
        FROM ( SELECT [XML] = CONVERT(XML, '<i>'
        + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')
          ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
      WHERE Item IS NOT NULL
  );
GO

これで、ストアドプロシージャは、ただ

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List VARCHAR(MAX)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT EmployeeID = Item FROM dbo.SplitInts(@List, ','); 
END
GO

そして、C# のコードでは、リストを '1,2,3,12' ...

テーブル値のパラメータを渡す方法は、それを使用するソリューションの保守性を簡素化し、XMLや文字列分割を含む他の実装と比較して、しばしばパフォーマンスを向上させることができますね。

入力が明確に定義されており（区切り文字がカンマかセミコロンかを推測する必要がない）、ストアドプロシージャのコードを検査しなければわからないような他の処理関数への依存がない。

UDTの代わりにユーザー定義のXMLスキーマを含むソリューションと比較すると、同じようなステップ数を伴いますが、私の経験では、管理、保守、読み込みがはるかにシンプルなコードです。

多くのソリューションでは、多くのストアドプロシージャで再利用するUDT（ユーザー定義型）は1つまたは数個しか必要ない場合があります。この例のように、共通の要件は ID ポインタのリストを渡すことであり、関数名はそれらの ID がどのようなコンテキストを表すかを記述し、型名は一般的なものにします。