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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More

Executing Store procedure with date

Meteoroid

Hi Everyone,

Good Evening,

 

I am experiencing an issue with executing my SQL store procedure by passing date parameter.

 

First issue i face is my parameter in the SP is @CreatedDate and the data type is see in SQL (SSMS) is Date but when I am calling the same SP in Alteryx its showing me as WString, I am not sure why the data type changes.

 

clipboard_image_0.png

clipboard_image_1.png

2. Second thing is I am not able to run the Store procedure, I am entering the value next to Datatype in the above window but it throughs an error as below

clipboard_image_3.png

 

Could anyone please help me in resolving this issue? Thanks in advance.

 

 

Kind Regards

 

Nebula
Nebula

Hi @msivakumar 

 

You didn't specify which database you're using but if it's SQL Server your Date parameter needs single quotes around it

 

Test_V2_Alteryx '2019-01-01'

 

Your second error is caused by a bug in the "Choose Table or Specific Query" window.  When you make changes, the space between the procedure name and the 1st parameter gets taken out.  "Test_V2_Alteryx 2019-01-01" becomes "Test_V2_Alteryx2019-01-01" with no space between the x and 2.  Click on "SQL Editor" button and add the space back in.

 

Dan

Alteryx
Alteryx

@msivakumar Here's an article talking about Stored Procedures in more details. 

 

@danilang is right, you might need to put in quotes/spaces for the dates in the SP.

 

for ex: 

EXEC Order_Priority_CriticalFilter 'Critical'

 

Digan
Alteryx
Meteoroid

Many thanks @Danilang

 

I have added an empty space and put the value inside quotes but now I receive a different error and moreover Could you please tell me why does the data type still remain in W-string and not changes to date. Please find the attached error I get after correcting the space and having them inside quotes. Thanks again.

Meteoroid

Thanks for the link DiganP, I followed steps Danilang and you suggested and it seems to be fine but I am still not sure why the datatype remains in W-String and not Date but my SP is created such that the Parameter is date. Should I change anything in my SQL query. I am receiving a new error now and I have attached the same. Thanks for your help.

Labels