現在作っているシステムでは、ビジネスロジックは、大部分をストアドプロシージャでコーディングしています。

通常のSQLからの利用や、SSIS経由のジョブからの実行、というだけでなく、SOAP化してSQL Server外からも実行するのが簡単で、再利用しやすいからです。（IISやNative XML Webサービスを使って。）

さて、そうこう進めているうちに、「1つのストアドの実行後、それだけで終わらず、処理結果を他のシステムと渡さないといけない」といった、状況が増えてきました。ビジネスロジックをアプリケーションサーバで組んでいれば、他システムとの連携はやりやすかったのかもしれませんが、上記の通り実装はストアドが中心。

処理結果を連携させる必要のある、もう一つのシステムにも、本来はDBの外から連携させた方が良いのですが、能力と手間の問題で、ストアドからストアドの呼び出し、という形で連携させてしまえば良いかな、と安易に考えてしまいました。（もう一つのシステムのデータソースも、同じSQL Serverのインスタンス上にあるので...）

ところが、これが大きな落とし穴になってしまいました(T_T)

「こっちのストアドから、そっちのストアドを呼び出せば良いのよね」と、もう1つのシステムの担当者と軽く話を進めたのですが、表面上は1つづつしかネストしないと思っていたストアド、お互い、既にその自身内に、いろんなストアドや関数をネストしていました。

また、ストアドの結果を受けて処理を分岐させるため、INSERT...EXECで結果を受け取っていたのですが、自分の担当分では正常に処理が終了するのに、さらに相手に自分の実装したストアドを実行してもらうと、以下のようなエラーが発生してしまい、正常に実行できません!

INSERT-EXEC ステートメント内では ROLLBACK ステートメントを使用できません。

また、それぞれのストアドには、Transaction処理（エラーが発生したら、Rollbackする）も設けていたのですが、何度修正しても、以下のようなエラーが発生してしまいました...。

EXECUTE 後のトランザクション数は、 COMMIT TRAN または ROLLBACK TRAN ステートメントに間違いがあることを示しています。以前の数 = 1、現在の数 = 0 です。

せっかく再利用できると思ってストアドを多用したのに、ほとんど同じコードを組み込み直さないといけないのかしら...と、だいぶ辟易していたころに、こんなサイトを見つけました。

[ How to Share Data Between Stored Procedures ] http://www.sommarskog.se/share_data.html#INSERTEXEC

ここに、以下のような一文が。

It can't nest. If some_sp tries to call some_other_sp with INSERT-EXEC, you will get an error message. Thus, you can only have one INSERT-EXEC active at a time. This is a restriction in SQL Server.

いつも参考にしていた本には、ストアドのネストの場合は、INSERT-EXECで結果を受け取れば良いという旨しか書かれていなかったので、こういう制限があるとは、分りませんでした....。（実際に、これって本当なんでしょうか？？）

また、ロールバックのエラーについては、こちらを呼んで、なんとか納得いたしました...。

[ Handling SQL Server Errors in Nested Procedures ] http://www.vfpconversion.com/Article.aspx?quickid=0305111

以前、アプリケーションサーバ上（J2EE/EJB)で、ビジネスロジックを組んだことがあるのですが、その時は、Commin/Rollback処理を含んだ1つのビジネスロジックを、さらに別のCommin/Rollback処理を含むビジネスロジックでラップした場合、トランザクションマネージャがうまく処理をしてくれていました。（一番上のビジネスロジックのトランザクションに合わせる）

そこでの理解が足りなかったのかもしれませんが、ストアドプロシージャもそういうことが可能と思って、安易にネストさせてしまっていました。これがいけないんでしょうね...。

また、本当は、他システムとの連携は、BASやらService Brokerを使ったりするほうが望ましいのですよね...。