This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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)
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.