Hi,
I'm trying to call a stored procedure in DB2 from SAS Base 9.2.
When I call a sp with only an IN argument it works as supposed, and I get the resultset I expect to get.
But when I try to call an extended version of the sp with the same IN argument, but an OUT parameter added I've got an IBM/DB2 CLI error.
The code I use is this:
proc sql;
connect to db2(database=&database user=&username password=&password);
%let out_status=2;
create table work.test as
select * from connection to db2
(CALL "SC40ADMIN".SPC40_HAMTA_NASTA_LADD_ID(1234, :OUT_STATUS));
disconnect from db2 ;
quit;
%put &out_status.;
This returns error:
ERROR: CLI prepare error: [IBM][CLI Driver][DB2/AIX64] SQL0313N The number of host variables in the EXECUTE or OPEN statement is not equal to the
number of values required. SQLSTATE=07004
SQL statement: CALL "SC40ADMIN".SPC40_HAMTA_NASTA_LADD_ID(1234, :OUT_STATUS).
I've tried to follow the instructions in SAS/ACCESS(R) 9.2 for Relational Databases: Reference, Fourth Edition and Stored Procedure That Passes Parameters is very similar to what I want to achieve.
You can only link query results to a from connection to statment. I think that stored procedures can be called from execute statement.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.