こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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 にしたいと考えております。

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

投稿日時 - 2008-12-14 15:19:13

QNo.4554991

すぐに回答ほしいです

質問者が選んだベストアンサー

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

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

投稿日時 - 2008-12-16 00:39:32

補足

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

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

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

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

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

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

投稿日時 - 2008-12-16 09:29:41

このQ&Aは役に立ちましたか?

2人が「このQ&Aが役に立った」と投票しています

-広告-
-広告-

回答(2)

ANo.1

>ストアド プロシージャで作成されたローカル一時テーブルは、ストアド プロシージャが終了すると自動的に削除されます。テーブルは、そのテーブルを作成したストアド プロシージャによって実行される任意の入れ子になったストアド プロシージャから参照できます。テーブルは、そのテーブルを作成したストアド プロシージャを呼び出したプロセスから参照することはできません。
・・と
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'

投稿日時 - 2008-12-15 21:26:53

-広告-
-広告-

あなたにオススメの質問

-広告-
-広告-