How do you run a stored procedure using PROC SQL?

[NBrown]

I am attempting to run a stored procedure using PROC SQL that runs more stored procedures underneath. Here is a sample of what I am doing:

%let RunSKey=51

proc sql;
     connect to odbc (DSN=connection);
     execute (
          DECLARE @RC int
          DECLARE @RunSkey int
          DECLARE @RunSection int

          SET @RunSkey = &RunSKey.
          SET @RunSection = 3

          EXECUTE @RC = [dbo].[storedProcedure]
               @RunSkey
               ,@RunSection
         ) by odbc;
          disconnect from odbc;
quit;
run; 

The stored procedure above runs more stored procedures depending on choice of @RunSection. Just for your information, I am able to run @RunSection = 1 and @RunSection = 2 just fine, each of which have a couple stored procedures underneath them. @RunSection = 3 has much more, at least 10, but I would not think that would matter. I can copy the SQL directly into SQL Server Management Studio and run it and it runs fine, so I don't believe there is a syntax issue.

If the @RC being declared an int bothers you, I have no idea why this is true (I inherited this SQL query). I do know that all three RunSections run fine in SSMS and the first two run fine in WPS, it's the third RunSection that seems to have issues.

Let me know if you need any more information to look into this. Thank you for any insight you can provide.

Edit:

Since I there are other @RunSections and this one runs consecutively after the second one, I have tried:

%let RunSKey=51

proc sql;

     connect to odbc (DSN=connection);

     execute (

          DECLARE @RC int

          DECLARE @RunSkey int

          DECLARE @RunSection int

          SET @RunSkey = &RunSKey.

          SET @RunSection = 2

          EXECUTE @RC = [dbo].[storedProcedure]

               @RunSkey

               ,@RunSection

          SET @RunSection = 3

          EXECUTE @RC = [dbo].[storedProcedure]

               @RunSkey

               ,@RunSection

         ) by odbc;

          disconnect from odbc;

quit;

run;

But this doesn't seem to help. It still runs RunSection 2 just fine, but not 3.

[SASKiwi]

I think you may have misspelt @RunSection

      EXECUTE @RC = [dbo].[storedProcedure]

               @RunSkey

               ,@RunSection

[NBrown]

Thanks for pointing that out. I'm not sure how it got in the copy/paste for the question, but it is not in the original script, so my issue still remains.

[SASKiwi]

So what happens when you run it in SAS? Please post the SAS log, including any messages, notes, errors.

[NBrown]

Ah yeah, that would be helpful. There were no errors in the log. The only thing the log states is:

NOTE: The statement completed successfully, however the database returned the following

      information:

      01000: [Microsoft][ODBC SQL Server Driver][SQL Server]Section 3

and that the statement successfully passed to the database. This is the exact same message that shows up in the log for the other run sections (except the "Section 3" part, of course).

[SASKiwi]

Not a lot to go on.... Try adding a semicolon after your stored proc code.

[Anotherdream]

Kiwi. Isn't there an option set in SAS that allows you to return notes from the Actual ODBC.. something along the lines of ODBC_Trace=,,,d;

I can't remember the exact option, and I could never get it to work myself, but I believe in some of your other posts you have brought this up before.

Hope that helps

[SASKiwi]

I know what you mean. I'm not at work at the moment so I'll have a look at this tomorrow.

I've also figured out recently how to get the return code off a stored procedure into a SAS macro variable which is proving to be really useful. I'll post that as well.

[SASKiwi]

The SAS option for tracing is: options sastrace = ',,,d' SASTRACELOC = SASLOG;

Here is an example of getting the stored procedure return code. The secret is putting it into a temporary table from where it can be read with SQL in a later step.

execute (

if object_id('tempdb..#MyReturnCode') is not null drop table #MyReturnCode;

create table #MyReturnCode (RetCode int);

DECLARE @RC int,

        @RetCode int

EXEC    @RC = MyStoredProcedure

SELECT  @RC as N'@RetCode';

INSERT INTO #MyReturnCode values (@RetCode);      
) by odbc;

To read your return code later and put it into a SAS macro variable:

proc sql noprint;

connect to odbc(your connection stuff here);

select RetCode into :RetCode TRIMMED

from connection to ODBC(if object_id('tempdb..#MyReturnCode') is not null select RetCode from #MyReturnCode);

quit;

[Anotherdream]

Over-writing my post because I just confirmed that stored procs do have default return codes that you don't have to code a "return" into.

Sorry for the confusion!

[SASKiwi]

Correct. But it is also possible for the stored proc to also contain developer-added return codes, in which case both are often collected and returned in an output parameter on the stored proc.

Apart from the restriction that no result set is allowed from the SAS EXECUTE statement, I've found copying SQL between SAS EXECUTEs and SQL Server Management Studio works fine.

The above example took a lot of mucking around to get right - I did find the basic method in a SAS Note which I could find for you if you are interested.