Help using Base SAS procedures

running sql server 2005 stored procedure from proc sql via sql pass-thru

Reply
Not applicable
Posts: 0

running sql server 2005 stored procedure from proc sql via sql pass-thru

Hi,
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:

proc sql;
connect to odbc as DW (dsn="blah" uid=xxxx pwd=xxxx);
create table SAMPLE as
select * from connection to DW (
BLAH BLAH BLAH
);
quit;

thanks!
Super User
Posts: 5,876

Re: running sql server 2005 stored procedure from proc sql via sql pass-thru

Posted in reply to deleted_user
Hi.
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).

Regards,
Linus
Data never sleeps
Not applicable
Posts: 0

Re: running sql server 2005 stored procedure from proc sql via sql pass-thru

I was able to get a result set back by calling this SQL stored procedure with two input parameters (in example below the parameters are 0, null) with this syntax:

proc sql;
connect to odbc as DW (dsn="blah" uid=xxxx pwd=xxxx);
create table TMP as
select *
from connection to DW (
EXEC dbo.spName 0,null
);
quit;
Ask a Question
Discussion stats
  • 2 replies
  • 176 views
  • 0 likes
  • 2 in conversation