Calling Stored Procedure x times based on list box values

[ITG1]

I have a workflow that takes 4 inputs, source (db connection), destination (db connection), period and a type.  When the user selects the source and destination from the drop down lists it eventually swaps those values into the input and replaces those strings with the appropriate db connection.

The problem is the other 2 which are list boxes which can be one or multiple values.  I am able to swap those values into my input and it returns the correct row counts regardless of if you selected 1 or multiple values, the problem I'm encountering is that a Post-SQL stored procedure has to be run after the data move to destination selection is made.

This would be fine if only one value was selected as the Stored Procedure design that exists only takes 1 of each parameter type at a time.  Unfortunately I do not know how many the user will select thus I'd have to iterate over this stored procedure x times passing those values in one at a time and executing it post-sql x times.

I suppose the easiest solution would be to alter the stored procedure to accept as many values as you want to pass in but I was hoping there was a way in Alteryx to:

Execute the stored procedure x times, passing in the drop down list values each time as we iterate over them but I haven't found a way to do that yet (easy enough to do in my SQL input statement to grab the data as we can just use "in", not so much in procedure).  Is this something an iterative macro might handle?  The overall workflow executes 5 other workflows (of which this is one) depending on which "type" is selected in another drop down list so the branching is getting a bit busy as it is, so I'm hoping to just insulate this problem within a small area if possible.

Thanks

[gabrielvilella]

You can run the same Stored Procedure multiple times using a batch macro, this will it will run one time for each selected value. I am just curious why you need to run the same procedure one for each selected value, are you not able to run just one query when having multiple values selected? 

[ITG1]

The legacy PL/SQL stored procedure only allows for 1 input parameter at a time, I'm trying to design an Alteryx process around it that can process it many times without changing the legacy procedure to accept multiple parameters.  How does the batch macro inherit the contents of the list box that the user selects?

Thanks

[gabrielvilella]

You have to send the value in a table, you can use a Text input for example. I encourage you to take a look at this article if you are not familiar with batch macros. 

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Getting-Started-with-Batch-Macros/t... 

[danilang · Accepted solution]

Hi @ITG1 

You pass the user inputs as batch parameters to the batch macro.  In your case you'd probably parse the user into multiple rows, one for each parameter combination entered.  Each of these rows is passed to the control parameter of the batch macro, which then iterates once for each row.  If the Post SQL command varies according to user input, then use a formula in the main workflow to build up a new command for each input and use the control parameter inside the batch macro to modify the string of the post SQL command on your output tool

Have a look at these videos for an introduction to macros and how to pass parameters into them

Dan

[ITG1]

Thanks Dan, that makes sense.

Cheers