Six Stars

Execute and parse result set of stored proc with multiple non-standardized selects

I have run into an issue where I am constrained to calling a stored procedure (mssqlserver) that contains about 8 different selects - none of which have the same schema.  I am trying to figure out the best way to call the stored proc and parse each record set, and since the results are returned using a different structure, I cannot just hook the SP component to the tParseRecordSet component (it demands a specific schema).  My current solution is just stripping each individual select from the SP and running them individually, but this is not ideal because the SP could change (not maintained by my group).  Any suggestions or ideas?

2 REPLIES
Moderator

Re: Execute and parse result set of stored proc with multiple non-standardized selects

Hello,


evansdar wrote:

I have run into an issue where I am constrained to calling a stored procedure (mssqlserver) that contains about 8 different selects - none of which have the same schema.  I am trying to figure out the best way to call the stored proc and parse each record set, and since the results are returned using a different structure, I cannot just hook the SP component to the tParseRecordSet component (it demands a specific schema).  My current solution is just stripping each individual select from the SP and running them individually, but this is not ideal because the SP could change (not maintained by my group).  Any suggestions or ideas?


What does your stored procedure with 8 different selects look like? Could you please set an example for us?

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Six Stars

Re: Execute and parse result set of stored proc with multiple non-standardized selects

To put it succinctly, it searches for different representatives at different institutions with certain titles.

So first query finds Directors and returns id, name, lname; second finds primary program coordinator and returns name,status,status2,id2; 3rd finds financial rep id,name etc.  Unfortunately for me this stored proc is part of a legacy app and I can only exec it, not modify. The result set of each of these are of different schema, so my trouble is how to get the correct data (really only need the id - it is common to all of the selects)