ストアドをまたがるローカル一時テーブル

質問

SQL Server 2005 でストアドプロシージャ（以降SPと省略）
を作成しています。

複数の SP から、構造が同じローカル一時テーブル
（テーブル名が # から始まるテーブル）
を使用したい為、
CREATE するだけのローカル一時テーブル作成SP、
DROP するだけのローカル一時テーブル削除SPを、
それぞれ作成しました。

次に、ローカル一時テーブルを使用したい SP 内で、
ローカル一時テーブル作成SP を EXECUTE 後、
ローカル一時テーブルに対し、INSERT をしたところで
以下のようなエラーとなります。
※INSERT の変わりに、SELECT にしてみても同様でした。

メッセージ 208、レベル 16、状態 0、プロシージャ TEST_SP、行 155
オブジェクト名 '#LocalWorkTable' が無効です。

※TEST_SP とは、ローカル一時テーブルを使用したい SP の名前
　#LocalWorkTable とは、ローカル一時テーブルの名前

グローバル一時テーブル
（テーブル名が ## から始まるテーブル）
に変更すると、正常に動作しましたが、
セッションをまたがって使用させたくないのです。

あと、
ローカル一時テーブル作成SPを使用せずに、
ローカル一時テーブルを使用したい SP 内で、直接 CREATE すれば
正常に動作しましたが、
上記にも書いたように、複数の SP で構造が同じローカル一時テーブル
を使用したいので、できれば、別 SP にしたいと考えております。

ストアドをまたがって、ローカル一時テーブルを使用する事は
できないのでしょうか？

回答No.2

jamshid6 · ベストアンサー

#1です。もう一つ代案を書いておきます。
質問者さんは、「テーブル作成SPを作って共用する」ということを考えておられるので、ストアドを１つ作る代わりにテンプレート用の実テーブルを１つ作るのも手かと思います。
・一時テーブルのレイアウトのテーブル(TEMPLATETBL)を準備
・一時テーブルを使うストアドでは、作成用のストアドを呼ぶ代わりに以下を実行する
SELECT TOP 0 * INTO #TEMPTBL FROM TEMPLATETBL

効果はたぶん同じです。
（DROPはDROP TABLE #TEMPTBLですが、先に書いた通り無理に実行しなくても基本的に自動でDROPされます）

補足

ご丁寧な回答、どうもありがとうございます。

構造が同じテーブルを作成する方法を、ずっと探していましたが、
見つけられないでいました。

テストして見たところ、この方法で十分対応できそうです。
後は、この一時テーブルに対し、CLUSTERED INDEX を作成し、
使用したいと思います。

１回目に頂いた回答も、
このような方法があるとは知りませんでした。

これで、ストアド作成の幅が広がりそうです。

どうもありがとうございました。

回答No.1

jamshid6

>ストアド プロシージャで作成されたローカル一時テーブルは、ストアド プロシージャが終了すると自動的に削除されます。テーブルは、そのテーブルを作成したストアド プロシージャによって実行される任意の入れ子になったストアド プロシージャから参照できます。テーブルは、そのテーブルを作成したストアド プロシージャを呼び出したプロセスから参照することはできません。
・・と
http://msdn.microsoft.com/ja-jp/library/ms174979(SQL.90).aspx
に書かれております。

したがって、ストアド間をまたがって使いたいなら
CREATE PROCEDURE sp_parentsp
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #TEMPTBL ([FLD1][.....)
EXEC (#TEMPTBLを使うSP1）
EXEC (#TEMPTBLを使うSP2）
EXEC (#TEMPTBLを使うSP3）
DROP TABLE #TEMPTBL
END
というように親のストアドで作成するしかありません。

したがって、CREAT/DROP部分を汎用化させる方法は各自の工夫次第です。あくまで一例として、その一時テーブルを使いたいSPをパラメータとして渡してしまうとか、です(あまりスマートとはいえませんが）。
CREATE PROCEDURE sp_parentsp(@execstr varchar(max))
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #TEMPTBL ([FLD1][.....)
EXEC (@execstr)
DROP TABLE #TEMPTBL
END

EXEC sp_parentsp 'EXEC sp_selectsp1;EXEC sp_insertsp'