Problem call DB2 stored procedure

[emil_karlsson_ferrologic_se]

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.

[LinusH]

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