topic Re: SQL stored procedure and passing today's date in User written code in SAS Data Management

SQL stored procedure and passing today's date in User written code

kjarvis — Mon, 02 Apr 2018 18:56:06 GMT

Hello,

I have a DI Studio job with a User Written transformation where I'm calling a SQL stored procedure. I need to pass today's date to the stored procedure. My code is giving me this error:

ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'convert'.

Here is the code:

proc sql;

connect to odbc ("dsn=SQL server Xxx; Trusted_Connection=yes; database=Xxxxxx");

execute (CCR_Rpt.spUpdate_Pers_Pref_Report @RunDate = convert(date, getdate())) by odbc;

What do I need to do to correctly pass today's date to the stored procedure?

Thank you for your help!

DI Studio 4.9

Re: SQL stored procedure and passing today's date in User written code

LinusH — Mon, 02 Apr 2018 19:41:07 GMT

Not an SQL Server specialist, but if you are letting the stored procedure fix the date you'll probably get more accurate help in an SQL Server forum.
Have you successfully called it outside SAS?

Re: SQL stored procedure and passing today's date in User written code

kjarvis — Mon, 02 Apr 2018 20:03:15 GMT

Linus - I have called several SPs via SAS successfully. This is the first time trying to use today's date for the parameter.

Thank you