[How To Execute Stored Procedures] 2 result sets from 1 stored procedure?

[How To Execute Stored Procedures] 2 result sets from 1 stored procedure?

  
Forge Component
(3)
Published on 2015-06-22 by Guilherme Pereira
3 votes
Published on 2015-06-22 by Guilherme Pereira

Guilherme hi,

Just wanted to know if with 1 stored procedure 2 result sets can be retrieved which have different structure.

Saludos,

Wilko

Hi Wilko,


That's not possible. I recommend you to take a look at the original forum post where this component is a sample fore here



Cheers,

Guilherme

Guilherme hi,

Thanks for the quick reply and the confirmation off what i already thought. We got a requirement from a client that has legacy systems with a stored procedure returning two result sets, but we'll explain them to modify the requirement / existing stored procedure.

You know about this line to redirect to the desired database connection? 

/* %LogicalDatabase%=GetLogicalDatabase({Customer}) */

the requirement is that client has 5 external databases on different database servers (i.e. one for each jurisdiction) that all have same table names and stored procedure names on different database servers. my thinking is to create external database links to all 5 databases and with integration studio connect to the customer table in all of the databases. In OS we rename each customer table to customer_jurisdiction1 and customer_jurisdiction2 etc.

if i need to call a stored procedure in the database of jurisdiction1 i use 

/* %LogicalDatabase%=GetLogicalDatabase({Customer_jurisdiction1}) */

and so on? You think that will work?

Thanks ahead,

Wilko

Solution

Hi Wilko,


I believe it may work. You need however to create 5 identical extensions, one for each different connection, and with one customer_jursdiction table.

While this may work you'd create a lot of code that you'd have to maintain separatly (e.g if customer jursdiction table changes you'd have to refresh on 5 different extensions).

Before going that path I'd recommend looking into the Runtime Public DB API which would allow you to still define and maintain the connections in ServiceCenter but you could build your could to dynamically open a specific connection and check the table / execute the SP without having multiple pieces of code.

The plus is that this API is the only supported way so if you get into a problem you'd be eligible for official support.


Hope this helps,

Guilherme

Solution

Guilherme thanks,

I will certainly explore that option of the db api.

Muito Obrigado,

Wilko