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.
... View more