Help using Base SAS procedures

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

Reply
N/A
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,256

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

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
N/A
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
  • 143 views
  • 0 likes
  • 2 in conversation