CODEWITHSUNDEEP
sqlsql-server-2008stored-proceduresdb2
plditallo
plditallo

Reputation: 701

SQL Server:exec('CALL' DB2; advice on managing result set columns

Techies--

If I were issuing an openquery select, my problems would be solved--but as far as i know openquery doesn't allow the calling lingo/w. parameter(s) to remote db2 servers! :)

Here's what works:

declare @z varchar(max);

set @z = '999990480,888887530';

exec ('CALL S1CATALOG.HCMDEV.EMP_ALL_STARS(?)',@z) AT DB2I;

This stored proc (EMP_ALL_STARS) accepts the concatenated string as a clob, then returns roughly 35 columns. Not all the applcations with an interest in utilizing this sproc need all 35 columns. Any advice on how to manage the result set?

Upvotes: 0

Views: 173

Answers (1)

Fred Sobotka
Fred Sobotka

Reputation: 5332

Do you have control over the remote DB2 procedure? If so, you could define multiple cursors inside the proc, each with a different set of columns in the result set. At runtime, when it's time for the proc to open a cursor that performs the query and returns the result set to the caller, a bit of logic can evaluate the relevant input parameters to determine which which cursor to open.

Upvotes: 1

Related Questions