calling stored procedure in SAS DI

[jdmarshg]

How does one go about calling a stored procedure in SQL Server... ?

I have tried many different variations of a proc sql statement.

I place this code within the precode of the SAS DI task.

I'm able to use the same proc sql statements to select from the database.

proc sql:

     EXECUTE (exec libref.storeprocname);

quit;

Any suggestions and or guidance would be greatly appreciated.

[SASKiwi · Accepted solution]

Does your EXECUTE code work OK outside of SAS DIS just in a normal SAS program?

I would expect to see something like this. Your connection string may vary. This one is based on connecting via an ODBC driver using Windows Authentification.

Proc sql;

  connect to MYSQL(noprompt = "server=MySQLServer;DRIVER=SQL Server;Trusted Connection=yes;DATABASE=MyDatabase;");

execute (

EXEC    @return_value = MySchema.MyProcedure

        ) by MYSQL;

Quit;

[jdmarshg]

Thank you so much that worked.

As I'm new to working with SAS and training is in the future.

I was trying to understand why a library would not work in this case and a pass through was needed but I believe I understand at this point.

[jdmarshg]

How would I go about passing macro variables into the stored procedure?

Can anyone point me in the right direction?

[SAS_1001]

How do I capture the stored procedure values into SAS Macro Variable. I'd like to create a SAS Macro Variable and persist through the DI Job ?