SQLServer 動的SQLで一時表を作成したい

Question

～やりたいこと～
SQLServer2008R2を使用しています。
あるテーブルに一時表の作成クエリが格納されているカラムがあり、これまではAccessVBAでそのカラムから取得したSQLをもとに一時表を作成しておりました。

今回、上記AccessVBAとは別に、SQLServerのメンテナンスプランで実行するストアドを作成することとなりましたが、このストアド内で同様に一時表を作成したいと思っています。

～問題点～
現状、一時表の作成クエリの取得はできているのですが、これをストアド内で動的SQLとして実行しても続く処理で一時表が認識できないという点です。

DECLARE @WSQL 	VARCHAR(4000)

--今回は固定のSQL文字列としていますが、本来はここで別テーブルからSQL取得しています。
SELECT @WSQL = 'CREATE TABLE #WK_TBL (COL1 VARCHAR (7) NULL ,COL2 INT NULL DEFAULT 0 )' 

EXEC (@WSQL)

INSERT INTO #WK_TBL (COL1,COL2) VALUES ('001',10) --ここでエラー発生。#WK_TBLがみつからない。

ちなみにEXECを使用せず、直接Create Tableを記述すれば問題なくINSERTできました。
また＃をつけず、通常表として作成・INSERTした場合にはEXECでも問題なく処理できました。

いろいろ調べてみたところ、EXECでSQL実行した場合は別セッションとして処理されているのでは？という文献を見かけました。
たしかに別セッションと考えれば納得いく動作なのですが、だとしたらどうすればいいのか、やはり無理なのか、よい対応方法をご存知の方がいればご教授いただきたいです。

この#WK_TBLはこのストアドが呼び出す次処理のストアド内でも参照され、また他の既存機能からも利用されているため、できればテーブル作成も埋め込みではなくカラムから取得したCreate文で実行したいです。
アドバイスのほどよろしくお願いいたします。

Answer 1

いろいろ調べてみたところ、EXECでSQL実行した場合は別セッションとして処理されているのでは？という文献を見かけました。

私は、以下のリンクを参考に動的テーブルを作成しました。
動的SQL

この#WK_TBLはこのストアドが呼び出す次処理のストアド内でも参照され、また他の既存機能からも利用されているため

ローカル一時テーブルを指定していますが、排他制御？等 扱いたいのであれば グローバル一時テーブルを検討してみては。

Comments

[jawa]

ご回答ありがとうございます。
そして説明が足りていなかったようで申し訳ありません。

#WK_TBLは既存の他システムでも利用されているテーブルのため、今回改修にあたり手を加えたくないという事情があります。
（質問文中に記載しました「次処理のストアド」というのも#WK_TBLを利用している既存システムのひとつです。）
グルーバル一時テーブルに変更するとなると、それら既存システムの改修も必要になってしまいますので避けたいところです。

ご紹介いただきましたページにつきましては、一時テーブルと呼称していますがローカル一時テーブルやグローバル一時テーブルではない通常のテーブルを使い終わった時に自分でDROPしているものです。
これも上記と同様の理由により今回は避けたいと思っております。

やはり動的SQLで一時表を作成するのは難しいのでしょうか。

[koutajero]

連結してみては？

-- 今回は固定のSQL文字列としていますが、本来はここで別テーブルからSQL取得しています。
SELECT @WSQL = 'CREATE TABLE #WK_TBL (COL1 VARCHAR (7) NULL ,COL2 INT NULL DEFAULT 0 )'

-- EXEC (@WSQL) コメント
Select @WSQL= @WSQL + ';'

@WSQL = @WSQL + INSERT INTO #WK_TBL (COL1,COL2) VALUES ('001',10)

EXEC (@WSQL)

[jawa]

たびたびのご回答ありがとうございます。

SQLを連結してExecというと全体のSQLが長くなる場合は厳しくなるかもしれませんが、今回最終的に作成したいものは上記のような一時テーブルの作成が２つと、後続のストアド実行が１つですので充分使えそうです。

参考にさせていただきます。