Named Return Parameters

You can declare names for the returned parameters of an SPL routine, or a name for the single value that an external function can return.

If an SPL routine returns more than one value, you must either declare names for all of the returned parameters, or else none of them can have names. The names must be unique. Here is an example of named parameters:

CREATE PROCEDURE p (inval INT DEFAULT 0)
RETURNING serial_num LIKE testtab.serial_num, 
   name LIKE testtab.name, 
   points LIKE testtab.points;
RETURN (inval + 1002), “Newton?, 100;
END PROCEDURE;

Executing this UDR would return:

serial_num    name      points
1002          Newton    100

There is no relationship between the names of returned parameters and the names of any variables in the body of the routine. For example, you can define a function to return an INTEGER as xval, but in the body of the same function, a variable declared as xval could be of the data type INTERVAL YEAR TO MONTH.