これも、覚書のようなものです。『知らなかった：ストアドプロシージャのネスト。』のポストの、その後になります。

Applicationサーバ（APサーバ）を使い、Javaなどでビジネスロジックをコーディングする場合は、ビジネスロジックを実装するクラスを複数利用して利用することが多くなります。この方法のメリットの1つは、出来たクラスを合わせて、さらに大きなサービスを提供できるクラスを作ることができることかな、と思います。

クラスごとにもちろんトランザクションの設定は必要ですが、複数のクラスをラップする、おおきなサービス専用のクラスを実装した場合は、トランザクションは一番外側のスコープで管理してくれます。（私が経験のあるのは、EJBだけですが...）

ところで、現在私は、ビジネスロジックの多くを、データベースのストアドプロシージャやファンクションで実装しています。

SQL ServerではストアドをWebサービスとして公開でます。この方法を使うと、アプリケーションサーバで他の言語でコーディングをする必要が無く、SQLだけ知っていればWebアプリが作りやすいからです。（もちろん、それなりに制限もありますし、全部をこの方法でまかなえるとは思っていません）

さて、こうして作るストアド。

この1つ1つに、EJB（のSessionBean）と同じように、処理単位でトランザクションの設定をしていました。最初は、個々のストアド単位でWebサービス化して利用していたからです。

この方法、最初のうちは良かったのですが...。

だんだんと他のシステムと連携したり、最初に作ったストアドをラップするようなストアドが必要になってきました。そうすると、APサーバでの経験上、ストアドの中で、別のストアドを実行すれば良いんだよね....と単純に考えてしまいました。

この考えのもとに、ストアドのネストをしてみたのですが、問題が発覚。　

とにかく、最終的にROLLBACKさせたいにしても、制限があって、こうなってしまうようです。

  • 内側のストアドからROLLBACK -> 外側のストアド(とSQL)でのROLLBACKだと、失敗してしまいます。
  • 内側のストアドからCOMMIT -> 外側のストアド(とSQL)でのCOMMIT / ROLLBACK はOK でした。   

最初は原因が分らなくて、かなり悩みました。

トランザクションを設定しなければ、失敗しないのですが、それは実際は無理。やっと原因がわかった時は、「ええ～？」と脱力してしまいました。

結局、こういう方針で進めていくことにしました。

  • 外側にトランザクションがある場合は、異常終了でもアプリケーションエラーでも、内側のストアドのトランザクションはCommitする。
  •  ただし、エラーの発生を外側に伝えないといけないので、戻り値を、かならず0以外にする。(-1とか1とか）
    • RETURN(-1)
  • 併せて、必要があれば結果を知らせるSELECTにはエラーコードとメッセージを返す。
  • ストアドを実行する側は、とにかく、まずはエラーコードを確認する。エラーがあれば必ずROLLBACKする。

また、外側のトランザクションに結果をSELECTで返す必要があり、外側で　INSERT ... EXECをしている場合は、こうします。

  • 正常の場合も異常の場合も、結果をSELECTで返す場合は、データ型、列の数を同じにすること。
  • ただし、INSERT....EXECにも制限があるので、注意すること。（後述します）

以下、こんなふうにしてみた、という具体例をあげてみます。

BEGIN TRY

-- 通常の処理がいろいろ.......
-- ロジック的にエラーがあった場合は、GOTO PROBLEMしています。

:
:
-- 終了フェーズ

/*--------------------------------------------
 (1)正常終了
　正常の場合、自分自身内も含め、BEGIN TRANSACTION ステートメントの宣言があったら、
　とにかくCommitさせます。


  (2)アプリケーション（ロジックエラー）および、例外発生時
　ローカルトランザクションだけでなく、外側にBEGIN TRANSACTION ステートメントの宣言があったら、
　最終的な判断は、外側に任せます。

-----------------------------------------------*/
-- 正常の場合、自分自身内も含め、BEGIN TRANSACTION ステートメントがあればCommit
IF @@TRANCOUNT > 0
 BEGIN
　/* SELECTし、外側でINSERT EXECで受ける場合はこちら。
    INSERT INTO @Result (0, "正常に終了しました。xxxxが処理されました。")
    SELECT * FROM @Result
　*/
    COMMIT TRANSACTION
 END
RETURN 0

-- アプリケーションエラーで飛ばした場合
-- @Error_Code, @Error_Messageはエラー原因がセットされています。

PROBLEM:
/* SELECTし、外側でINSERT EXECで受ける場合はこちら。
INSERT INTO @Result (Error_Code, Error_Message)
 VALUES (@Error_Code, @Error_Message)
SELECT * FROM @Result
*/

IF @@TRANCOUNT > 1 --外側でトランザクションが開始されている場合（自分自身＋αな場合）
 BEGIN
  --ここではCOMMITし、ROLLBACKを外側に任せる
  COMMIT TRANSACTION 
 END

ELSE IF @@TRANCOUNT = 1 --ローカルトランザクションのみの場合
 BEGIN
  ROLLBACK TRANSACTION --普通にROLLBACK
 END

-- ※どちらの場合でも、エラーコードを-1で返します
RETURN(-1)

END TRY

/*--------------------------------------------
 補足:
 
  CATCHできた場合でも、上記のPROBLEM:　でのパターンと同様に処理します。

-----------------------------------------------*/

BEGIN CATCH

/* SELECTし、外側でINSERT EXECで受ける場合はこちら。

SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage
*/
  -- デバッグ用
　-- PRINT ERROR_MESSAGE()

IF @@TRANCOUNT > 1 --外側でトランザクションが開始されている場合
 BEGIN
  --ここではCOMMITし、ROLLBACKを外側に任せる
  COMMIT TRANSACTION 
 END

ELSE IF @@TRANCOUNT = 1 --ローカルトランザクションのみの場合
 BEGIN
  ROLLBACK TRANSACTION --普通にROLLBACK
 END

-- どちらの場合でも、エラーコードを-1で返します
RETURN(-1)
END CATCH

上記は、ストアドの実行時、成功/失敗をリターンコード（0 or -1)で返すだけでなく、結果をメッセージとともにSELECTしてあげる場合として書いてみました。（Webサービス化する場合は、何かしらSELECTしてあげないといけないので）

また、ネストされている側の結果を、INSERT.....EXEC で受け取ることを想定しているので、正常終了の場合も、異常終了の場合も、SELECTで2つの列を返してあげています。

SELECTの結果は必要ない場合は、リターンコードで判断すれば大丈夫だと思います。

やはり、お手軽なだけに難点もあるんだなあ...と、改めて思いました。

また、もう一つ問題だったのが、ストアドプロシージャの結果を、INSERT......EXECで受け渡ししていたこと。上記で触れたINSERT....EXECの制限に当たります。

これも、ストアド単体、もしくは1回だけのネストのときは、問題が発覚しませんでした。

ところが、自分が用意したストアドを、同僚に渡して利用してもらったところで、問題が発覚。

INSERT EXEC ステートメントはネストできません。

とのエラーが発生し、大変頭を悩ませてしまいました。カーソルを使うのか、結果を受渡し用のテーブルに書き出すか、それとも、ネストしないようにAPサーバでコーディングするか.....。

かなり悩んだあげく、受け渡す結果はシステム間の処理番号、エラーコード、エラー（もしくは処理結果）メッセージの3つで、型も複雑でないので、OUTPUTを使うことにしました。

最初からこうしていれば...と思ったのですが、あまりOUTPUTの処理方法に慣れていなかったので、考え付かなかったため、時間がかなり無駄になってしまいました。

仕方がありませんので、今後の教訓にするしかないです（＾＾；

 以下、余談ですが...。

また、ストアドがネストしたりすると、それだけ処理に時間がかかります。自分だけでコーディングしているわけでは無いので、自分のストアドが思わぬところで呼ばれている可能性もあります。

こうなると、すぐに結果を返す必要のない処理は、できれば非同期にしたいと思うようになりました。ESBなどを使って、いったん処理を外に出したりして、そちらでハンドリングしてほしいな.....とか。

ただし、イベントドリブンの形で、ESBなどに依頼するとなると、ストアドの実行結果を受けて、それを外部に通知しないといけないので、OSコマンドをストアド内から実行するとか、CLRで外部にアクセスする必要も出てきます。

これはまだまだ私には難しい課題です。

ServiceBrokerを利用して、うまくできないかなあ...とも考えているのですが。