SQLServerでテンポラリテーブルを利用して処理を行う時に「オブジェクト名 ‘#tmp_table’ が無効です」と作ったはずのテンポラリテーブルが見つからず無効になってしまい、上手くいかなかったので、その暫定的な解決方法のメモ的な投稿です。
準備として下記のようなデータが入ったテーブル(account_table)を作成します。
SELECT year_month ,price FROM account_table
year_month price 201205 10005 201205 20005 201205 30005 201206 10006 201206 20006 201206 30006 201207 10007 201207 20007 201207 30007 201208 10008 201208 20008 201208 30008 201209 10009 201209 20009 201209 30009 201210 10010 201210 20010 201210 30010 201210 40010 201211 10011 201211 20011 201211 30011 201212 10012 201212 20012 201212 30012
次にこのテーブルからpriceが30000以上のものを抜き出して表示するストーアドプロシジャを作成します。
CREATE PROCEDURE get_account_table AS Declare @sql [varchar](4096)= '' --実験でテンポラリテーブルにデータ保存し、そのデータを取得する Select @sql ='' Select @sql = @sql + 'SELECT' Select @sql = @sql + ' year_month' Select @sql = @sql + ',price' Select @sql = @sql + ' INTO #tmp_table' Select @sql = @sql + ' FROM zz_test_table' Select @sql = @sql + ' WHERE price > 30000' EXEC(@sql) --テンポラリテーブル作成 --作成したテンポラリテーブルからデータを取得する Select @sql = '' Select @sql = @sql + 'SELECT' Select @sql = @sql + ' year_month' Select @sql = @sql + ',price' Select @sql = @sql + ' from #tmp_table' EXEC(@sql)
これを
EXEC get_account_table
と実行すると取得できるのかと思いきや
(9 行処理されました) メッセージ 208、レベル 16、状態 0、行 1 オブジェクト名 '#tmp_table' が無効です。
とエラーになってしまいます。
何故か作成したテンポラリテーブルが見つからないようです。
どうやら「EXEC()」でまたがっていると作ったテンポラリテーブルは別物になっているように思えます。
そこで苦肉の策で2つのSQLを「;」で連結して、1つの「EXEC()」で実行して見ました。
CREATE PROCEDURE get_account_table AS Declare @sql [varchar](4096)= '' --実験でテンポラリテーブルにデータ保存し、そのデータを取得する Select @sql ='' Select @sql = @sql + 'SELECT' Select @sql = @sql + ' year_month' Select @sql = @sql + ',price' Select @sql = @sql + ' INTO #tmp_table' Select @sql = @sql + ' FROM zz_test_table' Select @sql = @sql + ' WHERE price > 30000' -- EXEC(@sql) --テンポラリテーブル作成 --作成したテンポラリテーブルからデータを取得する Select @sql = @sql + ';' --2つのSQLを1つのSQLに連結してみる Select @sql = @sql + 'SELECT' Select @sql = @sql + ' year_month' Select @sql = @sql + ',price' Select @sql = @sql + ' from #tmp_table' EXEC(@sql)
EXEC get_account_table
すると
201205 30005 201206 30006 201207 30007 201208 30008 201209 30009 201210 30010 201210 40010 201211 30011 201212 30012
とりあえずは無事取得できました。
「EXEC()」は別のセッションになるのかな?
別セッションなあばテンポラリテーブルも別・・・なんでしょう。
とりあえずはこれで逃げておこう。
後日、詳細の調査が必要です。