01-22-2014 04:30 AM
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:
connect to db2(database=&database user=&username password=&password);
create table work.test as
select * from connection to db2
(CALL "SC40ADMIN".SPC40_HAMTA_NASTA_LADD_ID(1234, UT_STATUS));
disconnect from db2 ;
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, UT_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.
01-22-2014 10:44 AM
You can only link query results to a from connection to statment. I think that stored procedures can be called from execute statement.