I want to run a sql server stored procedure called "getdata" that has two parameters which returns a certain result-set, which if I were to call from within sql server query analyzer would look like this:
EXEC dbo.getdata 1,null
This runs the getdata stored procedure by passing in a "1" as the value of the first parameter, and a "null" as the value of the second parameter.
How do I call this from SAS? Here's how I normally use SQL pass-thru to get to the database in question:
connect to odbc as DW (dsn="blah" uid=xxxx pwd=xxxx);
create table SAMPLE as
select * from connection to DW (
BLAH BLAH BLAH
your option would might be using a EXECUTE statement in proc sql. It allows you to call stored procedures within your DBMS. But it has the following limitation (from on-line doc):
"However, stored procedures with output parameters are not supported in the Pass-Through Facility."
If your result set is other than just a return code (obtained by &SQLXRC and &SQLXMSG), I think you have to find another way to pass the result to SAS (using some other interface or store the result as an external file).