読者です 読者をやめる 読者になる 読者になる

きよくらの備忘録

「三日坊主と呼ばせない!日記」改め。主にソフトウェア開発関連の話題。

テーブル値パラメータについて少し整理とメモ その3・動的SQL編

SQL Server

id:sady_nitroさん!

先日エントリに挙げていたこの件!パラメタライズドな動的SQLで正しく型を指定してあげると、行けました!

sadynitro.hatenablog.jp

何のこと?

詳細は上記のid:sady_nitro のエントリを参照していただくとして、おおざっぱにいうと、『T-SQL内で動的にSQLを組み立てて実行するとき、そのままではテーブル値パラメータ (Table-Valued Parameters / TVPs) がうまく使えない』というお話でした。

が、先ほどとふと思い立って、sp_executesqlを使ってみたら実行できました

sp_executesql を使った動的SQLのパラメタライズド

動的SQLをパラメタライズドされた形で実行するにはsp_executesql を利用します。

TVPsも、これを使って正しくパラメータを設定してやると、動的SQL内で利用できるようです。

例は以下になります。パラメータを定義するときのポイントはREADONLYキーワードをつけること*1

CREATE PROCEDURE [dbo].[DynamicSqlProc]
  @Values IntValueTableType READONLY
AS
BEGIN
  DECLARE @sql NVARCHAR(MAX);
  DECLARE @paramDef NVARCHAR(MAX);

  -- SQLを組み立て
  SET @sql = N'SELECT * FROM @Values;';

  -- パラメータを定義
  SET @paramDef = N'@Values IntValueTableType READONLY'; -- READONLYは必須

  -- 実行:第三引数以降にパラメータの組み合わせを列挙
  EXEC sp_executesql @sql ,@paramDef,
                      @Values = @Values;

  RETURN 0;
END

*1:これが無いと実行時にエラーになる