複数のストアドプロシージャ (stored procedure) でローカルテンポラリテーブルを共有する

ローカルテンポラリテーブルを共有する

先日来より悩んでいたSQLServerでの

• SQLServerで作ったテンポラリテーブルが見つからず無効になってしまう
• イントラマートでSQLServerの一時テーブルがうまく使用できない

の解決策が見つかりました。

親で作って子に渡す

結論としては「親のストアドプロシージャ (stored procedure) でテンポラリテーブルを作って子のストアドプロシージャ (stored procedure) で参照する」です。

言われてみれば悩むこともない事でした。(^^ゞ

まずは親ストアドプロシージャ (stored procedure)です。

CREATE  Procedure [dbo].[test_main]
AS

    CREATE TABLE #tmp_table ( no [SMALLINT] , name [VARCHAR](30))

    INSERT INTO #tmp_table VALUES (1, '太郎' )
    INSERT INTO #tmp_table VALUES (2, '次郎' )
    INSERT INTO #tmp_table VALUES (3, '三郎' )

    EXEC dbo.test_sub1
    EXEC dbo.test_sub2

子ストアドプロシージャ (stored procedure)です 。

CREATE  Procedure [dbo].[test_sub1]
AS
    --親プロシジャで作成されたテンポラリテーブルを検索
    SELECT no, name FROM #tmp_table ORDER BY NO

CREATE  Procedure [dbo].[test_sub2]
AS

    --親プロシジャで作成されたテンポラリテーブルを検索
    SELECT no, name FROM #tmp_table ORDER BY NO DESC

これを呼び出すと

    EXEC test_main

結果は

no   name
1    太郎
2    次郎
3    三郎

no   name
3    三郎
2    次郎
1    太郎

ちゃんとマニュアル読めば書いてます

ちなみにヒントは

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

と言うことで解決です。