BookmarkSubscribeRSS Feed
NBrown
Calcite | Level 5

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.

10 REPLIES 10
SASKiwi
PROC Star

I think you may have misspelt @RunSection

      EXECUTE @RC = [dbo].[storedProcedure]

               @RunSkey

               ,@RunSection

NBrown
Calcite | Level 5

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
PROC Star

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

NBrown
Calcite | Level 5

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
PROC Star

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

Anotherdream
Quartz | Level 8

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
PROC Star

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
PROC Star

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
Quartz | Level 8

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
PROC Star

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 11326 views
  • 6 likes
  • 3 in conversation