Re: Help with UDFs or stored procedures

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

Dan - other than the SQL Reference at InfoCenter - always go to 
InfoCenter first- the standard book for UDFs and SPs is located at 
http://www.redbooks.ibm.com/redbooks/pdfs/sg246503.pdf

A stored procedure cannot be used in a SELECT - that is where a UDF 
shines. A UDF can be implemented with a service program - so you 
could have the same service program for both a UDF and for CALLP in 
your RPG/LE programs.

The other possibilitiy is using the SQL SET or VALUES statement in 
RPG to execute a UDF and have the result put into a local host variable.

Oh - be sure to use VARCHAR in the CREATE FUNCTION or CREATE 
PROCEDURE statement - this will let you use constants instead of 
field names in your call of either, because literals are converted to 
variable length values by SQL. Also, declare you incoming parameters 
as VARYING.

HTH
Vern

At 03:38 PM 12/15/2006, you wrote:

> (Cross-posted to midrange-l and rpg400-l)
>
> Our legacy database files are littered with dates defined as four numeric
> fields, with a 2-digit century, 2-digit year, month, and day.  I must work
> with these dates in SQL and WrkQry, sometimes in RPG.  I would like to
> define either a UDF or stored procedure or *something* that would convert
> four input parameters into a date result.  It would be nice if I could use
> the same function in an SQL statement and in RPG code, but not a
> requirement.
>
> So, I need to read up on how to do this, and how to choose UDF vs. stored
> procedure.  I searched the archives, saw a few examples of code, but did not
> see any reference on how to call the UDF or stored procedure.
>
> Can anyone suggest good online references?
>
> TIA,
> Dan
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.