SQL Proc return DS

[adarsh_kr]

Hi all,

Sorry if it was already answered, but I am trying to find a way to read the result set of a SQL procedure (EXT) inside a SQLRPGLE program and wondering how I could do it.

Thanks in advance,
Adarsh

[Scott Klement]

http://systeminetwork.com/article/re...result-set-rpg

[adarsh_kr]

Thanks Scott. Appreciate your help.

[Lynne Noll]

I have a number of general parameter style RPG procedures that return array or mod data structures as result sets. I set up CLI to process the result sets (and they work nicely, and work with procedures that return cursors, and can work between different AS400's, and works for SQL procedures, and other parameter styles, and are all around cool. But cumbersome, even with a service program to help.Not more cumbersome than doing the same thing in .net, though--Rpg is way better.)

However, for cases where the procedure is called locally from RPG programs as well as from client applications, I found the simplest way of returning the result set was to call the procedure outside of SQL (as an ordinary procedure) and just return the number of elements and array in optional parameters. When the routine is called without the extra parameter, it returns a result set; otherwise, it returns the number of elements and array.