2015年3月 – 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 (ユーザー定義テーブル型)

月: 2015年3月

SQL Server プログラムからストアドにデーターテーブルを渡す方法