Stored Procedure - Dynamic Input with Declared Variables

[nmosley]

I am attempting to call a stored procedure via the dynamic input tool.  I have had success call the SP via the Stored Procedure pane of the Dynamic Input tool by providing static values.  I utilized Modify SQL Query via the Replacing a Specific String with a date supplied in my Alteryx workflow.  That was simple enough... Now I am attempting to add another layer of complexity and am returning some errors.  Hoping they are just syntax rather than attempting something that cannot be done.  I would like to declare a couple of date variables in SQL and supply one of these to the SP.  It works well enough in SQL but dropping into the SQL editor results in an error in the workflow.

Oddly the error says "Error opening "SELECT * FROM Declare...." even though my code doesn't begin with select *?

See a scaled down version of what I am attempting to do below.  Essentially comparing my supplied value to the actual data in the table prior to the SP being called.   There is more detail in the actual process but even a scaled down version doesn't seem to allow both a declared variable and a call to the SP.  This was placed in the SQL Editor pane.

Declare @DateInput as date

SET @DateInput = 'DateString'

SET @DateInput = (SELECT MAX(CycleDate) FROM tblWarehouse PA WHERE CycleDate<=@DateInput)

Exec [spTest] @DateInput

Possible solutions. 

Making the edit to the SP directly is off the table as this is used elsewhere.

I can query the table in the workflow and arrive at the same value for @LastCycleDate but would prefer the SQL route.

How to clean up the call to the SP?

[dougperez · Accepted solution]

Why don't you create the dates in your workflow and pass it as a parameter to the dynamic input? You cannot do what you want (edit the variable into sql editor...)

[nmosley]

Ahh I hate arriving at "You cannot do what you want" lol.  I ended up with 2 dynamic inputs.  The first solves for the correct date variable then supplies it into a second dynamic input that calls the stored procedure.

[Garabujo7]

Hello @nmosley ,

Have you tried to call the SP using the pre or post sql?

And you may combine it with the dynamic input to modify the parameters on the fly.

Gabriel