【SQLServer】動的SQLとテーブル値変数は相性が悪い

※2015/04/10 更新
↓本件の解決策をご教示いただきました kiyokura.hateblo.jp

今後はこの実装方法で行きたい所存。

---

SQLServerではストアドプロシージャの引数として配列を渡すことができないので、テーブル値変数を利用します。しかし、テーブル名の指定を動的に変更したい等の理由で動的SQLを使用している場合、その内部でテーブル値変数を使用することができません。

↓今回使用するテーブルとサンプルデータ

動的SQLでなければ、もちろん普通にテーブル値変数を使用できます。

DECLARE @table TABLE(
  UserId varchar(50) NOT NULL,
  Age smallint NOT NULL
)
--ストアドの場合は引数として渡される
--ここでは直接値を詰め込んでおく
INSERT INTO @table
VALUES('suzuki', 32)

SELECT U.UserId, U.Age, U.Point 
FROM   Users_0001 AS U 
       INNER JOIN @table AS T
       ON U.UserId = T.UserId

動的SQLで同様の構文を積み込んで実行するとエラーになります。

DECLARE @table TABLE(
  UserId varchar(50) NOT NULL,
  Age smallint NOT NULL
)

--ストアドの場合は引数として渡される
--ここでは直接値を詰め込んでおく
INSERT INTO @table
VALUES('suzuki', 32)

DECLARE @mySql varchar(max)
DECLARE @GroupId varchar(4) = '0001'

--以下の書き方ではエラーとなる
SET @mySql = 'SELECT UserId, Age, Point FROM User_' + @GroupId + ' AS U'
SET @mySql = @mySql + ' INNER JOIN @table AS T'
SET @mySql = @mySql + ' ON U.UserTd = T.UserId'
EXECUTE(@mySql)

回避策としては、テーブル値変数を一時テーブル等に詰め替えて、動的SQL内では詰め替え先のテーブルを参照する方法があります。

DECLARE @table TABLE(
  UserId varchar(50) NOT NULL,
  Age smallint NOT NULL
)

--ストアドの場合は引数として渡される
--ここでは直接値を詰め込んでおく
INSERT INTO @table
VALUES('suzuki', 32)

--一時テーブルを生成して
CREATE TABLE #temp(
  UserId varchar(50) NOT NULL,
  Age smallint NOT NULL
)

--詰め替える
INSERT INTO #temp
SELECT * FROM @table

DECLARE @mySql varchar(max)
DECLARE @GroupId varchar(4) = '0001'

SET @mySql = 'SELECT U.UserId, U.Age, U.Point FROM Users_' + @GroupId + ' AS U'
SET @mySql = @mySql + ' INNER JOIN #temp AS T'
SET @mySql = @mySql + ' ON U.UserId = T.UserId'
EXECUTE(@mySql)

この方法により、無事に以下の結果を得ることができました。

考え得る限りの方法を試してみた結果、上記の方法に行き着いた感じですが、より良い方法があればぜひ知りたい所存です(何せ業務に直結するもので…)