テーブル値パラメーターとユーザー定義テーブル型

テーブル値パラメーターとユーザー定義テーブル型

「テーブル値パラメーター」（Table-Valued Parameters）は、Step 2.11 で説明した「ユーザー 定義テーブル型」をストアド プロシージャの入力パラメーターとして指定できる機能で、SQL

Server 2008 から提供された機能です。これを利用すると、IN 演算子をパラメーター化する場合

に非常に便利です。

テーブル値パラメーターは、次のように利用します。

-- テーブル値パラメーター（ユーザー定義テーブル型を入力パラメーターとして利用）

CREATE PROCEDURE ストアド プロシージャ名

@パラメーター名 ユーザー定義テーブル型 READONLY , …

AS

任意の Transact-SQL ステートメント

パラメーターのデータ型を指定するところへユーザー定義テーブル型を指定し、READONLY（読 み取り専用）キーワードを付与して利用します。

ユーザー定義テーブル型は、Step 2.11 で説明したように table データ型のテーブル定義に対し て、名前を付けてデータ型（Type）として保存できる機能です。復習になりますが、ユーザー定 義テーブル型は、次のように利用します。

-- ユーザー定義テーブル型 CREATE TYPE 型名

AS TABLE

( 列名1 データ型 ,列名2 データ型 , … )

go

-- ユーザー定義テーブル型の利用 DECLARE @変数名 型名

Let's Try

それでは、テーブル値パラメーターを利用して、IN 演算子をパラメーター化してみましょう。

1. まずは、IN 演算子へ与える値を格納するための int データ型の列を持ったユーザー定義テー ブル型を「valuelist」という名前で作成します。

-- ユーザー定義テーブル型 CREATE TYPE valuelist AS TABLE ( val int ) go

2. 次にテーブル値パラメーターを利用して、ストアド プロシージャを作成します。

-- テーブル値パラメーターを利用するストアド プロシージャ CREATE PROCEDURE proc3

@v valuelist READONLY AS

SELECT * FROM emp

WHERE empno IN ( SELECT val FROM @v )

IN 演算子の部分をサブクエリとし、@v（ユーザー定義テーブル型 valuelist を指定したパ ラメーター）から val 列（int データ型の列）を取得して、それを IN 演算子へ与えていま す。

3. 次に、ユーザー定義テーブル型の val 列へ「1」と「5」を格納して、この値をストアド プロ シージャのパラメーターへ与えてみます。

-- ユーザー定義テーブル型を利用した変数@v の宣言 DECLARE @v AS valuelist

-- 変数 @v へ値の格納（IN 演算子に与える値）

INSERT INTO @v (val) VALUES ( 1 )

,( 5 )

-- ストアド プロシージャの実行 EXEC proc3 @v

empno が 1 と 5 の社員を取得できていることを確認できます。

このように、ユーザー定義テーブル型とテーブル値パラメーターを利用すると、IN 演算子の パラメーター化が簡単に行えるようになります。

Tips： ADO.NET からテーブル値パラメーターを利用する場合

VB や C# などのアプリケーションからテーブル値パラメーターへ値を利用する場合は、SqlParameter クラ

スのデータ型として「Structured」を指定して、DataTable クラスへ値を格納しておくようにします。具体的 には次のように利用します（VB の場合）。

Imports System.Data

Imports System.Data.SqlClient Imports System.Data.SqlTypes ：

' DataTable オブジェクトへ値を格納 Dim dt As New DataTable

Dim row As DataRow

dt.Columns.Add("val", Type.GetType("System.Int32"))

' val 列へ 1 を格納 row = dt.NewRow() row("val") = 1 dt.Rows.Add(row)

' val 列へ 5 を格納 row = dt.NewRow() row("val") = 5 dt.Rows.Add(row)

Dim cnstr As String = "Server=localhost;Database=sampleDB;Integrated Security=SSPI"

Using cn As SqlConnection = New SqlConnection(cnstr) cn.Open()

Using cmd As SqlCommand = New SqlCommand() cmd.Connection = cn

cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "proc3"

' SqlParameter のデータ型へ Structured を指定

Dim p1 As SqlParameter = cmd.Parameters.Add("@v", SqlDbType.Structured) p1.TypeName = "valuelist"

p1.Value = dt

Using dr As SqlDataReader = cmd.ExecuteReader() While dr.Read()

Debug.Print(dr("empno") & vbTab & dr("empname")) End While

End Using End Using End Using

empnoが1と5 の社員を取得