SQLServerで作ったテンポラリテーブルが見つからず無効になってしまう

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()」は別のセッションになるのかな?
別セッションなあばテンポラリテーブルも別・・・なんでしょう。
とりあえずはこれで逃げておこう。
後日、詳細の調査が必要です。

タイトルとURLをコピーしました