ストアドから得られるレコードセットを、別のストアドで利用したい

質問

現在、実行するとあるレコードセット（１行１列のみ）を返すストアドプロシージャProc_Aがありますが、このプロシージャを実行して得られる結果を、
別のプロシージャProc_Bで、以下のような形で使用したいと考えています。

ただし、Case文内部でのストアドの実行や、変数へのストアドの実行結果の代入を試しましたが、どちらもうまくいっていません。

なにか、よい解決策があれば、教えていただければ、と思います。

パターンA)　Case内部で直接実行

select
case Table_A.param_1
when 0 then Exec Proc_A '***','**'
else ''
end

パターンB）いったんパラメータとして取得
declare @values as char(10)
execute sp_executesql N'@values = Proc_A ''***'',''**''', N'@values char(10) OUTPUT', @values OUTPUT

回答No.1

xcrOSgS2wY

あまり自信はないのですが、なかなか回答が付かないようですので・・・

私もちょっと試してみたのですが、ストアドプロシージャが返すレコードセットを別のSQL分のパラメータとして使用するのは、普通の方法では無理そうでした。

そこで、「普通の方法では無理」ということを前提にした上で、次の方法はいかがでしょうか。

(1) ストアドプロシージャの構造を変える

Proc_Aがレコードセットを返すのではなく変数に値を返す構造になっていれば、問題はないわけです。

ですので、Proc_Aと同じレコードセット（1行1列）をパラメータとして返す新しいストアドプロシージャProc_A'を作成し、それを使用するのはいかがでしょうか。

なお、現状でProc_Aも必要としているのであれば、Proc_AとProc_A'でほぼ同じクエリが重複してしまうので、Proc_AはProc_A'を使用する形に書き直すほうがより好ましいかと思います。

(2) 超が付くほど強引な方法です

ストアドプロシージャProc_Aの内容（SQL文）を取得することは可能なので、それをそっくり取得して、その中にあるSELECT ...をSELECT @var=...に変更してダイナミック実行し、取得した値をパラメータとして返すのはいかがでしょうか。

Proc_Aが自前のストアドプロシージャであれば(1)のように書き換えてしまえばいいわけですが、ヨソのストアドプロシージャであって内容がいつどのように変わるか定かではないという場合には、こんな方法を取るのもやむを得ないのかなと思います。

お礼

ありがとうございます。

結局この件については、(1)の方法で対応を取ることにしました。
もともとのプロシージャに手を加え、オプションの値によって、レコードセットと変数と、値の返し先を変更できるようにしました。

このような感じです。
こうすることで、もともとのプロシージャを利用している部分でも書き換えが不要になりました。

---------------------------------
create proceadure Proc_A
@value ...,
@opt as int = 0,
@out as varchar(100) OUTPUT
as

...処理...

set @value = 結果

if @opt = 1
set @out = @value
else
select @value

GO
---------------------------------