[SQLServer]「INSERT EXEC ステートメントはネストできません。」を対策する

はじめに

こんにちは。私は仕事でSQL Serverを使っています。
今回はSQL Serverのネタです。

ストアドプロシージャは便利ですよね。
複数箇所で同じく登場するSQLを、共通メソッドのように実装することもできます。

-- ストアドプロシージャ
CREATE PROCEDURE GetKonkiAnime
AS
BEGIN
    SELECT
        X.anime_id,
        X.anime_name,
        Y.on_air_time
    FROM m_anime X
    INNER JOIN m_konki_anime Y ON X.anime_id = Y.anime_id;
END

-- 実行
CREATE TABLE #KonkiAnime
(
    anime_id int,
    anime_name varchar(200),
    on_air_time time
);

INSERT INTO #KonkiAnime
EXEC GetKonkiAnime;

例なので、せめてビューにしろよというツッコミは置いといてください。

問題点

では、ストアドプロシージャはどんな状況でもメソッドと同じように扱えるのかと言うと、そうではありません。
タイトルの通り、ストアドプロシージャのSELECT結果を取得して、呼び元でINSERTする処理は、ネストができません。

-- ストアドプロシージャ1
CREATE PROCEDURE GetKonkiAnime
AS
BEGIN
    SELECT
        X.anime_id,
        X.anime_name,
        Y.on_air_time
    FROM m_anime X
    INNER JOIN m_konki_anime Y ON X.anime_id = Y.anime_id;
END

-- ストアドプロシージャ2
CREATE PROCEDURE GetOsusumeKonkiAnime
AS
BEGIN
    CREATE TABLE #KonkiAnime
    (
        anime_id int,
        anime_name varchar(200),
        on_air_time time
    );

    INSERT INTO #KonkiAnime
    EXEC GetKonkiAnime;

    SELECT
        X.anime_id,
        X.anime_name,
        Y.comment
    FROM #KonkiAnime X
    INNER JOIN t_osusume_anime Y ON X.anime_id = Y.anime_id;
END

-- 実行
CREATE TABLE #OsusumeKonkiAnime
(
    anime_id int,
    anime_name varchar(200),
    comment varchar(500)
);

INSERT INTO #OsusumeKonkiAnime
EXEC GetOsusumeKonkiAnime;    -- GetOsusumeKonkiAnime内でもINSERT EXECしているのでエラー

「INSERT EXEC ステートメントはネストできません。」や
「INSERT EXEC ステートメントは入れ子にはできません。」
といったエラーが出ます。

上記の例は非常に簡単なSQLなのですが、実際のストアドプロシージャでは、複雑な処理をした後でのSELECT文なことも多いわけです。

SELECT結果のテーブルを返したいので、値を行で持てないOUTPUT変数では代わりにできません。
ストアドプロシージャのSELECT結果を、メソッドの戻り値的に使うことは多いので、ネストできない仕様は厄介です。

ググると出てくる対策

「仕様です」
「諦めてください」
「ネストしない実装を目指してください」
といった助言が出てきます。
それで解決するなら困ってないわ。

ボツにした対策は…

ストアドプロシージャの処理全文を呼び元にコピペ

当然、動きはします。
ですが、万策尽きた時の最終手段としましょう。
簡単なSQLなら良いですが、それならばストアドプロシージャにはしていないですね。

ストアドプロシージャの処理をテーブル値関数化する

SQL Serverに用意されている機能を使う案ですね。
できるなら、これが一番スマートではあります。
ですが、テーブル値関数では、一時テーブルが使えなかったり、ストアドプロシージャは呼べなかったり。
色々と制限が多く、ストアドプロシージャを簡単にテーブル値関数に組み替えられないことが多かったです。
また、ストアドプロシージャを呼び出している既存のプログラムに影響するのも良くないです。
テーブル値関数は呼び出しがEXECではありませんからね。
同じ処理内容のストアドプロシージャとテーブル値関数を両立する…のも考えたくはないですね。

OPENROWSET関数を使う

できるそうですが、この関数を使ったことはないです。
どうやら、外部からSQL ServerへログインしてSQLを実行して結果を得る、みたいな方法らしいです。SQL Serverに限らず色々な物へアクセスできるとか。
ただ、実行時にはログイン情報を入れなければならず、採用しませんでした。
ログイン情報テーブルからデータを取得できればできなくもなさそうですが…求めているものと違う方向へ突っ走っている気がしてなりません。

今回の対策

ということで、違うアプローチでの対策を考えました。
前述のストアドプロシージャ1はそのまま使います。

-- ストアドプロシージャ
CREATE PROCEDURE GetOsusumeKonkiAnime
    @Key varchar(10) = NULL
AS
BEGIN
    CREATE TABLE #KonkiAnime
    (
        anime_id int,
        anime_name varchar(200),
        on_air_time time,
    );

    INSERT INTO #KonkiAnime
    EXEC GetKonkiAnime;

    SELECT
            X.anime_id,
            X.anime_name,
            Y.comment
    INTO #KonkiOsusumeAnime
    FROM #KonkiAnime X
    INNER JOIN t_osusume_anime Y ON X.anime_id = Y.anime_id;

    IF (@Key IS NULL)
    BEGIN
        SELECT
            X.anime_id,
            X.anime_name,
            X.comment
        FROM #KonkiOsusumeAnime X;
    END
    ELSE
    BEGIN
        -- temp_osusume_konki_animeは実テーブル
        INSERT INTO temp_osusume_konki_anime
        (
            temp_key,
            anime_id,
            anime_name,
            comment
        )
        SELECT
            @Key,
            X.anime_id,
            X.anime_name,
            X.comment
        FROM #KonkiOsusumeAnime;
    END

    DROP TABLE #KonkiOsusumeAnime;
END

-- 実行
DECLARE @Key varchar(10);
/* 何らかの方法でユニークキーを生成して@KeyにSET */

CREATE TABLE #OsusumeKonkiAnime
(
    anime_id int,
    anime_name varchar(200),
    comment varchar(500),
);

EXEC GetOsusumeKonkiAnime @Key;

-- INSERT EXECの代わり
INSERT INTO #OsusumeKonkiAnime
(
    anime_id,
    anime_name,
    comment
)
SELECT
    X.anime_id,
    X.anime_name,
    X.comment
FROM temp_osusume_konki_anime X
WHERE X.temp_key = @Key;

呼び出し元でINSERT EXECを回避するという方法になります。
temp_osusume_konki_animeは以下のように定義しておきます。

(
    temp_key varchar(10),    -- PK
    anime_id int,            -- PK
    anime_name varchar(200),
    on_air_time time,
)

SELECT実行結果＋キーのデータを持つ実テーブルを介することで、INSERT EXECを擬似的に再現しています。

引数の@Keyは省略できるので、既存のプログラムで、

EXEC GetOsusumeKonkiAnime;

という風に、INSERTはせずにEXECのみでストアドプロシージャを呼び出していた箇所も今まで通り動作します。

今まで@Keyなしで実行していた箇所は、ストアドプロシージャ実行でSELECT結果が出てくることを期待していて、
これから@Keyありで実行する箇所は、ストアドプロシージャ実行のみではSELECT結果が出ないことを承知して実装するからです。

今回の対策の問題点

SELECT実行結果保存用のテーブル（temp_osusume_konki_anime）にはデータが溜まり続けてしまいます。
定期的にクリアしてあげると良いと思います。
実行時、1時間より前に登録されたデータを削除するとか。

また、テーブル更新があるのでトランザクションもつけるべきです。
今までSELECT文だけだったストアドプロシージャにINSERT文が入ってくる可能性があり、違和感があるかもしれません。

おわり

指摘事項がございましたら、コメントにてお知らせくださると幸いです。
改善の余地はありそうですが、ご覧頂きありがとうございました。