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.
I'm looking for some advice (step-by-step instructions) how to pass a dynamic parameter/field to a Stored Procedure during execution time?
Background: I have a Stored Procedure that looks for the following parameters (@d_startDate, @d_endDate...) during the execution.
Outcome: I need to declare the Start Date as today's date minus N number of days. (example using SQL: GETDATE()-30) The same goes for the End Date where it needs to be set similarly so it can query the right amount of data within the rest of the workflow.
Question: I read some posts where it seems like I can use a Dynamic Input to accomplish this, but I haven't had any luck making it work. Has anyone had any luck passing dynamic parameters in Alteryx? Can you help explain or post screen shots of how to get it to work?
Hey @michaelperillo, While brief, nice to have bumped into you at TC! Dynamic Input tool does seem to be the tool you'd want to use. Can you clarify on why you aren't having luck using it? Is it not generating the data as you expect or are you receiving an error on attempt? I use this tool frequently, but don't believe that I've ever used it to update an SP. However, being that it does have an options specifically for this, I would suspect that using it would be similar to the ways I've used to update an SQL query.
It was good seeing you again, too. I actually haven't used the Dynamic Input in any of my workflows. That being said, I'm not sure how it is connected or configured to allow a dynamic date to be passed to the stored proc. See my screenshot below.
I want my StartDate to default to a date that is N number of days from today's day, and the EndDate to be a day behind today's date. Not sure how to configure or format that to work with Dynamic Input
Assuming that the start and end date are in the data going into your Dynamic input tool, it should just be a matter of adding 2 "SQL: Update Stored Procedure" options. The thing that you need to make sure of though is that your input to this tool only has one record of data. Each record = single request.
Throw a summarize tool before the Dynamic input tool to generate you your single record that represents your two dates.
I'll see if I can mock something up on my computer to share some screenshots...
@jrgo I've got the workflow working. Your workflow walk-thru helped, but I was missing that piece to make the date dynamic.
Tried and Failed:
I tried adding the text input tool (and yes I know it's a manual add) but I added a formula field in there just to see if magically it would work; it didn't
I tried adding in the formula tool and added in the info for the startdate / enddate, but it said it needed a starting connector to work. sooo close!
@Treyson jumped on a WebEx and set me straight. He had me add the text input with a variable I was going to use anyway, to subtract from one of the dates. UGGGH, sometimes I overthink (or not at all) things.
With some additional playing around with 'Modify SQL Query' section, I ended up using "Replace a Specific String". For whatever reason the update stored proc value threw more errors. I don't care at this point, it's working magically.
Including a screenshot of the three tools used to make my date dynamic input flow through to my StoredProc! Thanks again @jrgo and @Treyson for all your help.