Post SQL Create & Changing variable

[Samanthaj_hughes]

Hi all,

Just getting back into Alteryx after a whole 10 months off after having baby #2 and I am drawing a blank - BABY BRAIN! I am used to being able to answer these questions, so not in my comfort zone by asking. Please be kind, as I do expect this one to be easy. :D Plus thought I would test the power of this community for the first time.

I want to execute a stored procedure passing variable through... I am using 8.6 and 10.1, so it would be useful to know if the example works in both versions.

e.g EXEC dbo.storedprocedure @id = variable;

Can I use a variable in PostSQL Create box on an output? (Variable is created during the workflow after finding the max of the database table and then adding 1)

TIA!

S*

[RodL · Accepted solution]

First of all, congrats on the new baby!! 

And I wouldn't categorize this as "easy" myself (expecially since no one has jumped on this question like happens so often). My assumption from your description is that your "end result" is really intended to be just running the stored procedure. Would that be accurate?

If so, you can put the command to execute the SP in the Post SQL window, but the trick would be to make that "dynamic". Others may come up with something better, but I'm thinking there could be two ways to do this. 

First, you could create a batch macro where the input is the single variable value you create in your workflow. Inside the macro would be an output that connects to the database (and since it's an output, then you would need to write to something...possibly a "dummy table" that could be dropped within the same Post SQL?) Then the Action tool in the macro would update the value of the variable. (You'd also need to add a Text Input tool to the Output...since it needs something coming in...that could be just dummy data that would write to that dummy table.)

A second alternative is dependent on if you are doing this in a SQL Server platform (since that is all that is supported currently in Alteryx from an Input tool standpoint). What I'm thinking here would be to have your workflow with the variable value feed into a Dynamic Input tool and actually configure your Input to run the SP "natively" in the database configuration. Then you would just modify the SQL Query to Update Stored Procedure with the variable value. Again, assuming that the end point is the actual running of the SP, then you would just need to add a Browse after the Dynamic Input tool that would just show the variable value.

Hope this makes sense. 

Thanks for the question!

Rod

[Samanthaj_hughes]

Thanks Rod, I am going to try these now. Really appreciate your response :D

[Samanthaj_hughes]

Just to keep you all updated, I used a batch macro and passed the variables through which are then used in the PostSQL statement to execute the stored procedure. :) Thank you Rod.

[artzee1556]

Hi Comet. So thankful for your post. I am facing the same challenge of needing to pass parameters to the post SQL statement in the Dynamic Input tool. I have a stored procedure all set up but I just don't know how to embed it in the app and then set up a macro to pass it parms. Can you provide a little more detail or, even better, attach the code snippet that shows how to do this? Thanks again, Art, Marietta, GA

[artzee1556]

How goofy I am. Samantha, not Comet! Apologies.

[Samanthaj_hughes]

Hi Marietta,

Yes in the end I used a macro which I feed the values into and use the Formula action to update the Pre SQL in an output to EXEC Stored_Procedure to include the variable from the action input. See below image, this should give you enough to get going there is an example of a single variable and multiple variables here, essentially its just expanding the string to include them as you would in SQL, but using the Alteryx [#1] to fill in the blanks. Good luck. 

Example of running a Stored Proc from Alteryx

[Branden_SAG]

Samantha--

I understand the process you have illustrated, however, I'm not sure about what you are writing in your preSQL. Is there a reference from the expression you built in the action tool?

Thanks,

Branden

[Samanthaj_hughes]

Hi Brandon, yes the action tool is set to update the pre or post SQL on the Output tool. When you set your action tool, you will need to select how you want to action (drop down) then from the list of configurations options you can select which one you want to update. In this case I chose update action using a formula and updated the SQL from the config.

Hope this helps.

S*