Calling a DB2/i Stored Procedure from ABAP

[Former Member]

Hello Group,

I am trying to figure out how to manage the result set of a stored procedure called from ABAP code.

I have created the SP in DB2/I using System I Navigator. I call it from Navigator (call schemaname.spname('inputparameter')) without any problem. The result set is displayed in the screen.

Now, I want to call the same SP from ABAP and manage the result set within the program.

The SP uses only one input parameter to filter data. The result set is approximately 10 columns and 1,500 rows.

Any help or code excerpt will be appreciated.

Thanks,

Ricardo Quest

DB2 DBA

[dorothea_stein]

Hi Ricardo,

Stored procedures are good to execute some logic on the database server w/o getting a result back, and IBM i has a long tradition of that by allowing you to execute CL commands from SQL. (There are also a variety of DB2 SQL stored procedures.)

It is also possible to define stored procedures that return a result set, but that has never been the preffered way on IBM i. I'd encourage you to use/create (table) functions instead, because that's the more natural and powerful way to do it: Just like stored procedures they allow you to specify parameters, but as an advantage over stored procedures you can use both as part of regular queries.

Table functions can even be used in most places of the SQL syntax where you could use a normal table allowing you to embed the result into more complex queries.

This is the syntax how to retrieve the result set of a table function, if it is called <my_function>:

     SELECT * FROM TABLE (<my_function>(<parm1>, ..., <parmn>)) MY_RESULT

This is the basic syntax how you'd create it. <your logic> can probably stay the same as it is in the stored procedure:

     CREATE FUNCTION <my_function> (<parm1> <type1>, ..., <parmn> <typen>)
     RETURNS TABLE (<col1> <type1>, ..., <colm> <typem>)
     BEGIN
       <your logic>

       RETURN <result>;
     END

Best regards,

Dorothea