How To? Pass Dynamic Parameters/Fields to a Stored Procedure

[michaelperillo]

Hello Community!

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?

Thanks!

[jrgo]

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.

Kind regards,

Jimmy 

[michaelperillo]

Hey @jrgo,

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

[jrgo]

@michaelperillo,

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]

Hope this helps...

[michaelperillo]

@jrgo Thanks for attaching the animation.  However, I'm trying to figure out how to make the inputs dynamic (not manually inputting new data).

As an example, the start date should be 30 days in the past, and the end date should reflect yesterday's date.

Is it possible to add a formula into a Text Input field or dynamic input field? Does that make sense what I'm trying to do?

[jrgo]

@michaelperillo,

Yeah, a formula can do that for you. so your end date could be something like 

= DATETIMEDIFF(DATETIMETODAY(),-1,'days')

and your start date would be 

= DATETIMEDIFF([end_date],-30,'days')

[michaelperillo · Accepted solution]

@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. 

 

[jrgo]

@michaelperillo, glad to see you got your solution! I usually use the 'replace a specific string' as well, but wasn't sure how well it'd work with with SP's... apparently better than the obvious :)

Thanks for sharing!

Best,

Jimmy