Re: Return result set with Stored procedure in V6R1

On Thu, Mar 17, 2016 at 10:47 AM, Wilson, Jonathan <
piercing_male@xxxxxxxxxxx> wrote:

I think the idea was to create a file (or some other method) with a
number of records, then return the records as a list of records to the
calling SQL.

Kind of, select * from my_stored_procedure(bykey) Or what ever kind of
syntax is applicable.

​For this you'd want a User Defined Table Function (UDTF)

select *
from table ( myudtf(parm1)​) as rs

Then my_stored_procedure would then do some stuff and then return X
number of records.

I have no idea if such a thing is possible. My vastly _limited_
understanding of SP's is that they are singular in nature - they only
work on a single "record" between the caller and called.

​There are three types of ​what are known in the ANSI/ISO standards
as Persistent Stored Module (PSM)
- Stored Procedures
- User Defined Functions
- triggers


​Stored procedures (SP) are basically programs. In fact, on the IBM i,
every *PGM object can be called as an SQL stored procedures; though it's
easier if you define the interface to the RDBMS ahead of time.
SP's have a defined input/output parameter list. They can optionally
return a result set of 1 or more rows.

User defined functions are available in two types
- Scalar (UDF), input parms along with a single returned value that can be
used in expressions. Often the function is invoked for every row of a
table.
--- SELECT myfunc(fld1) from mytable
- Table (UDTF), input parms along with an output set of rows that can be
reference in a FROM clause

Triggers
- Fixed set of input/output parms. Invoked automatically by the RDBMS.
​
​Charles​