Problem call DB2 stored procedure

emil_karlsson_ferrologic_se — Wed, 22 Jan 2014 09:30:17 GMT

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.

Re: Problem call DB2 stored procedure

LinusH — Wed, 22 Jan 2014 15:44:44 GMT

You can only link query results to a from connection to statment. I think that stored procedures can be called from execute statement.