community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Post SQL Create & Changing variable

Highlighted

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*

Alteryx Alumni (Retired)

First of all, congrats on the new baby!! :smileyvery-happy:

 

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

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

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.

Labels