Table型のパラメータ引渡し

.NetでSQL ServerのStoredProcedureの実行時、パラメータとしてテーブル型を渡す方法

SQLサーバに対象となるTable型を定義する

CREATE TYPE dbo.MyTableType AS TABLE
    ( id int, name varchar(50) )

MyTableTypeと言う名前で型を追加

テスト用データを保存するテーブルを作成

CREATE TABLE [dbo].[TableTypeTest](
	[id] [int] NULL,
	[name] [varchar](50) NULL
)

StoredProcedureのパラメータに設定する

Create procedure [dbo].[TableTypeTest] 
@mytable as dbo.MyTableType READONLY
AS

begin 

insert into dbo.TableTypeTest
select * from @mytable

end

1. テーブルタイプの引数にはREADONLY指定が必要である
2. パラメータ変数をテーブルな様に使用出来る
3. Insert,Updaet,Deleteも同様

.Net側からの呼び出し方法

'ストアドプロシージャ用変数
Dim inParam(0) As SqlParameter
Dim outParam As SqlParameter
Dim resultset As DataSet

'戻り値の設定
outParam = New SqlParameter("SqlReturn", SqlDbType.Int)
outParam.Direction = ParameterDirection.ReturnValue

inParam(0) = New SqlParameter("@mytable", SqlDbType.Structured)

Dim dt As New DataTable
dt.Columns.Add("id")
dt.Columns.Add("name")
Dim dr As DataRow = dt.NewRow

inParam(0).Value = dt

_SqlExe.Exec("NewCategoryTableAdd", CommandType.StoredProcedure, inParam, outParam, resultset)

1. SqlParameterのSqlDbTyoeを「Structured」に設定する
2. テーブル型と同じ構造のDataTableをパラメータとして設定する

内部の変数としての使い方

declare
@myVal as dbo.MyTableType

insert into @myVal (1, 'aa')
update @myVal set id = 2 
delete @from @myVal

SqlDbType.Structuredで利用可能な形

現在としてはDataTableのみ
IList形ではエラーになった

Null扱いに付いてた補足

下記の場合、Nullとして設定される

1. 未設定
2. null/Nothing
3. DBNull.Value

参考URL : http://msdn.microsoft.com/ja-jp/library/bb675163.aspx