投稿者: t_nary

VB.NETなどのプログラムからSQL Serverのストアドへテーブルデーターを渡すには、ユーザー定義テーブル型を利用する。
なお、ユーザー定義テーブル型はSQL Server2008以降で使用できる。

手順(1) ユーザー定義テーブル型の作成と権限設定

    CREATE TYPE [dbo].[UserTableTypeInt1] AS TABLE(
    [id] [int] NULL
    )

    ストアドで使うには作成したユーザー定義型に実行(EXECUTE)権限を設定する必要がある。
    GRANT EXECUTE ON TYPE::[dbo].[UserTableTypeInt1] TO ユーザー名;

    ManagementStudioから権限を設定する場合は、
    [プログラミング]-[種類]-[ユーザー定義テーブル型]で、作成したテーブル型を右クリックでプロパティーから権限を設定するが、なぜか実行権限は表示されないので制御(CONTROL)権限を設定する。
    制御権限を設定するとその他の全ての権限も暗黙的に設定される。

手順(2) ストアドの作成

    CREATE PROCEDURE stTest1
       @a1 UserTableTypeInt1 READONLY — ユーザー定義テーブル型
    AS
    BEGIN
        UPDATE Table1 SET c1 = 0 WHERE ID IN (SELECT id FROM @a1)
    END
    GO

手順(3) 呼び出し側プログラムの作成

    Dim cn As SqlClient.SqlConnection

    Private Sub Test()
        Dim cmd As New SqlClient.SqlCommand
        Try
           ‘ストアドに渡すDataTableの作成
            Dim dt1 As New DataTable
            dt1.Columns.Add(“ID”, GetType(Integer))
            dt1.Rows.Add(1)
            dt1.Rows.Add(2)
            dt1.Rows.Add(3)

            ‘ストアドの実行
            cmd.Connection = cn
            cmd.CommandText = “stTest1”
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Clear()
            cmd.Parameters.Add(“@a1”, SqlDbType.Structured)
            cmd.Parameters(“@a1”).TypeName = “UserTableTypeInt1”
            cmd.Parameters(“@a1”).Value = dt1 ‘DataTableをパラメーターとして渡す
            Dim res1 As Integer = cmd.ExecuteNonQuery()
        Finally
            cmd.Dispose()
        End Try
    End Sub

---

参考：
https://technet.microsoft.com/ja-jp/library/bb522526 (ユーザー定義テーブル型)