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

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

<a href="http://sadynitro.hatenablog.jp/entry/2015/03/25/224609">【SQLServer】動的SQLとテーブル値変数は相性が悪い - Jack of all trades</a>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