topic Re: running sql server 2005 stored procedure from proc sql via sql pass-thru in SAS Procedures

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

deleted_user — Tue, 02 Sep 2008 19:36:34 GMT

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!

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

LinusH — Thu, 04 Sep 2008 07:47:07 GMT

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

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

deleted_user — Thu, 11 Sep 2008 19:56:24 GMT

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;