Stored Procedure outputs

[Allan1]

Hi, I have a stored procedure with 2 result sets and need to bring both in to the workflow. I've seen this question posted before, but it didn't appear to have an answer. Can anyone help with this? Thanks

[MichalM]

@Allan1 

We currently support Stored Procedures for the below 3 data sources

• Microsoft SQL Server
• Oracle
• SAP Hana

You can find more details on Stored Procedures and Alteryx here.

If you would like to bring in 2 results sets into the workflow, you will need to use two Input data tools each configured to connect to the relevant stored procedure.

Michal 

[Allan1]

Thanks Michal, but I've already seen this page and it hasn't helped me. Also, you say 'use two Input data tools each configured to connect to the relevant stored procedure', but I don't know how to differentiate between the two outputs with the 2 input tools. Please could you be a bit more detailed? Also, a related issue is that my stored procedures don't show on my stored procedure tab when using an input tool. Is there a setting I should change? Thanks 

[MichalM]

@Allan1 

Could you please provide a bit more context on what you're trying to achieve once you bring the data from the two stored procedures into Alteryx? Also, what is the database you're connecting to?

Once you configure the Input Data tool to connect to the database of your choice, the stored procedures tab should list all of the stored procedures your user (or user credentials of which you'v used to authenticate) has access to.

[Allan1]

Thanks Michal. My priority problem is that I have 1 stored procedure on a SQL Server that returns 2 data sets and I need both in my workflow. Is this possible? Also, my secondary issue, which is mainly just an irritation, is selecting stored procedures when opening an input tool:

 

Thanks

[MichalM]

Assuming these tables have different schemas I don't think this would be possible. What I'd probably suggest is re-creating the stored procedure in Alteryx for those 2 tables. Do you know what the stored procedure does in the background?

[Allan1]

Thanks again, I was hoping just to pick up both result sets from the stored procedure to ensure the calculations remained up to date, in case they were amended and added to in the future. To replicate either table in the workflow would entail quite a bit of processing. As it looks as though Alteryx can't pick up more than 1 result set from a stored procedure, it looks as though I'll need to do that though. Thanks for your help.

[jpaplanus]

Hi Allan, were you ever able to figure out how to work with a stored procedure that was returning multiple datasets? I have a similar situation and have been scouring the community and internet with no success thus far. 

[Allan1]

Hi. No, I'm afraid I wasn't able to find a solution. In the end I sourced the second result set from somewhere else and just used the first result set, which was ingested as a default from the stored procedure.