現在作っているシステムでは、ビジネスロジックは、大部分をストアドプロシージャでコーディングしています。
通常の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を使ったりするほうが望ましいのですよね...。