Name return parameters

You can define names for each return parameter of an SPL UDR. Specify the names in the RETURNS/RETURNING clause of the CREATE PROCEDURE/FUNCTION statement.

The syntax for the CREATE PROCEDURE/FUNCTION statement is:

RETURNS/RETURNING data_type AS return_param_name [{, data_type AS
return_param_name}]

The return_param_name parameter defines the name of the return parameter and follows the same rules as for table column names. Either all return parameters should have names or none should have names. The names of the return parameters for a function or procedure should be unique. Return parameter names cannot be referenced within the body of the procedure. There is no relation between the names of the return parameters and any variables within the function or procedure itself, as shown in the following example:

CREATE PROCEDURE NamedRetProc()
RETURNING int AS p_customer_num, char(20) AS p_fname, char(20) AS 
p_lname;

DEFINE v_id int; 
DEFINE v_fname char(15); 
DEFINE v_lname char(15);

FOREACH curA FOR SELECT customer_num, fname, lmname 
    INTO v_id, v_fname, v_lname FROM customer
RETURN v_id,v_fname, v_lname WITH RESUME;
END FOREACH;

ENDPROCEDURE;

The NamedRetProc() procedure returns data with the return parameter names as the returned values, instead of the name expression that appears if you do not name return parameters:

p_customer_num p_fname p_lname
       101     Ludwig   Pauli
       102     Carole   Sadler

Avoid naming return parameters if you intend to export the database to a pre-9.4 version of HCL OneDB™ that does not support this syntax. When you export a database containing stored procedures that have names for return parameters, the schema creation scripts also have these names. If you try to import the database using a pre-9.4 version of HCL OneDB, errors will be returned. If you decide to go ahead and import the stored procedures without the names for return parameters, you can manually edit the schema creation scripts to be able to import.

Tip: When you call a stored procedure in the projection list of a SELECT statement, return parameter names are not displayed. Instead, the output string “expression” appears. If you want to display the return parameter name, use the AS keyword, as in: SELECT some_func(a,b) AS name1,... .