Re: SQL UDF to call RPG without service program

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

I think we are agreeing a bit violently on some of this!   :) I'm still 
focusing on the basic feature that IBM i SQL will treat a call to an 
existing program as a stored procedure call with GENERAL style and 
IN/OUT parameters - at least, that's what I see in the docs. And if the 
RPG or even CL changes the values of a parameter, that change is 
available to what did the CALL.

Now I hear you say, and it seems sensible, that to get the changed 
parameter value, you need to do the CALL within some kind of SQL 
environment, be it function or procedure or maybe dynamic compound 
statement - and maybe in RSS if you declare some variables and use them 
in parameters for a CALL - I'm just dreaming, now.

*Regards*

*Vern Hamberg*

IBM Champion 2025 <cid:part1.sE4PMd9g.AVQFu9wz@centurylink.net> CAAC 
(COMMON Americas Advisory Council) IBM Influencer 2023


On 3/3/2026 11:46 AM, Charles Wilt wrote:
> Vern,
>
> You can get something back from stored procs or functions.
>
> Functions can only return 1 piece of data (could be an array now-a-days) ,
> stored procs can have multiple output (or IN/OUT) parms.
>
> Just like an RPG Program, an SQL stored proc usually does some
> significant chunk of work.  Whereas a a SQL function or RPG Procedure tends
> to do some smaller (usually repeated) work.
>
> Repeated invocation is the point for UDFs, as shown in Roberts example:
> SELECT
>         ivwhid AS "Warehouse",
>         ivitm# AS "Item Number",
>         GETUPCHARGEFUNC(ivwhid, ivitm#) AS "Up Charge"
>    FROM invmasp
>   WHERE ivwhid = 90
>     AND ivitm# = 5
>   ORDER BY 1, 2;
>
> Charles
>
> >