NZPLSQL procedures as functions

A routine that returns a non-null value of data type other than binary integer or Boolean is automatically converted to a MODIFIES SQL DATA compiled SQL function. You can then invoke this function in the same way as any other compiled SQL function. A routine that returns a null value, or a binary integer, or a Boolean can also be created as a function by explicitly replacing the PROCEDURE keyword with the FUNCTION keyword.

These procedures that are created either implicitly or explicitly as functions must be dropped by using DROP FUNCTION instead of DROP PROCEDURE.

You can't call these created functions by using the CALL statement but you must use VALUES instead. If you must use a CALL statement, create an SQL PL procedure that calls the NZPLSQL function. The SQL PL procedure must match names and arguments of the function and define the return type as an output parameter.

Example

CREATE PROCEDURE MYPROC(INTEGER)
RETURNS VARCHAR(32)
LANGUAGE NZPLSQL
AS
BEGIN_PROC
BEGIN
RETURN 'ABC';
END;
END_PROC;

CREATE PROCEDURE MYPROC(IN myvar INTEGER, OUT output VARCHAR(32))
LANGUAGE SQL
BEGIN
SET output = MYPROC(myvar) ;
END