SQL Server 2008 のユーザー定義テーブル型とテーブル値パラメータ その1
SQL Server 2008 からは、「ユーザー定義テーブル型」と「テーブル値パラメータ」がサポートされました。
ユーザー定義テーブル型(User-Defined Table Type)は、ユーザー定義の Table データ型に対して、名前を付けて型(Type)として保存できる機能です。
テーブル値パラメータ(Table-Valued Parameters)は、ユーザー定義テーブル型をパラメータ(ストアド プロシージャなどの引数)として指定できる機能です。
これは次のように試すことができます。
-- ユーザー定義テーブル型の作成 CREATE TYPE testTableType AS TABLE ( a int, b varchar(100) ) go -- テーブル値パラメータを利用したストアド プロシージャの作成 CREATE PROCEDURE testProc1 @tvp testTableType READONLY AS INSERT INTO t1 SELECT * FROM @tvp
作成したユーザー定義テーブル型へ値を代入し、それをストアド プロシージャへ渡して実行するには、次のように記述します。
-- ユーザー定義テーブル型を利用した変数 @tvp1 の宣言 DECLARE @tvp1 AS testTableType -- 変数 @tvp1 へ値の格納 INSERT INTO @tvp1 (a, b) VALUES ( 7, 'AAAAAA') ,( 8, 'BBBBBB') -- ストアド プロシージャの実行 EXEC testProc1 @tvp1 -- 結果の確認 SELECT * FROM t1
そのほかの SQL Server 2008 の新機能の具体的な利用方法については、弊社執筆の SQL Server 2008 自習書シリーズ(下記 URL)の「SQL Server 2008 の注目の新機能をイチ早く試してみよう! 」編を参考にしてみてください。
http://www.microsoft.com/japan/sqlserver/2008/self-learning/default.mspx
ユーザー定義テーブル型とテーブル値パラメータ その2 IN 演算子の値リストをパラメータ化
SQL Server 2008 からの新機能であるユーザー定義テーブル型とテーブル値パラメータは、"配列" のように利用することもできるので、IN 演算子の値リストをパラメータ化する目的でも利用できます。これは次のように試せます。
-- ユーザー定義テーブル型の作成 CREATE TYPE valuelist AS TABLE ( val int ) go -- テーブル値パラメータを利用するストアド プロシージャの作成 CREATE PROCEDURE testProc2 @v valuelist READONLY AS SELECT * FROM t1 WHERE a IN ( SELECT val FROM @v )
ユーザー定義テーブル型の val 列へ「1」と「5」、「7」を格納して、この値をストアド プロシージャの IN 演算子へ与えて実行してみます。
-- ユーザー定義テーブル型を利用した変数 @v の宣言 DECLARE @v AS valuelist -- 変数 @v へ値の格納(IN 演算子に与える値) INSERT INTO @v (val) VALUES ( 1 ) ,( 5 ) ,( 7 ) -- ストアド プロシージャの実行 EXEC testProc2 @v
このように、ユーザー定義テーブル型とテーブル値パラメータを利用すると、配列のように利用することができるので便利です。なお、VB や C# などのアプリケーションからテーブル値パラメータを利用する方法は、本自習書シリーズの「開発者のための Transact-SQL 応用」編を参考にしてみてください。
http://www.microsoft.com/japan/sqlserver/2008/self-learning/default.mspx