Date:         Tue, 24 Apr 2007 22:24:56 -0400
Reply-To:     David Hopkins <dhopkins@EMAIL.ERS.USDA.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         David Hopkins <dhopkins@EMAIL.ERS.USDA.GOV>
Subject:      Using Proc SQL with MS SQL Stored Procedures
Content-Type: text/plain; charset="iso-8859-1"

Dear SAS-L Gurus,

I have a Proc SQL question.

I do not have much experience with Proc SQL going against MS SQL databases.  

In our current situation, we need to use, I think, the EXECUTE statement in SAS's Proc SQL to run a stored procedure on our MS SQL database.

Does anyone have experience with this?  Do you have any sample code to jump start us into action?

Basically we need to run a MS SQL stored procedure through SAS Proc SQL and get back records from the MS SQL to create a SAS dataset.

Here's a piece of our code, in rough draft form.

-------------------------------------

*****************************************;
*                                                                                ;
* The following section does the actual                   ;
* data retrieval.                                                       ;
*                                                                               ;
*****************************************;

Proc SQL;

     Connect to odbc 
        (noprompt="filedsn=&FileDSN" );

     Create Table &SASDataSetName as         /**** This was the old section ****/
         Select * From connection to odbc 
          ( select &SelectClause from &AllPur   A
            where     
                 &WhereClause
                                            );

     EXECUTE( sp_SASNielsonList ) ;                /**** This is the new section, using a stored procedure, I think ****/


     Disconnect from odbc;

Quit;

run;


------------------------------------

I have a number of questions on this.  Here are my first two.

#1 -- The CREATE TABLE command, does that go with the EXECUTE statement?  The stored precedure would return records to create a SAS dataset.

#2 -- Inside the EXECUTE statement, we would like to pass in 3 or 4 parameters, with the stored precedure to make the stored procedure flexible.  Can this be done?  ..... through the EXECUTE parentheses?


Any help or examples on this would help.

Thank you guys!


--David Hopkins
  DHopkins@email.ers.usda.gov

   Cool web site:   www.ers.usda.gov