Add display labels

You can use CREATE FUNCTION to create a routine that specifies names for the display labels for the values returned. If you do not specify names for the display labels, the labels will display as expression.

In addition, although using CREATE FUNCTION for routines that return values is recommended, you can use CREATE PROCEDURE to create a routine that returns values and specifies display labels for the values returned.

If you choose to specify a display label for one return value, you must specify a display label for every return value. In addition, each return value must have a unique display label.

To add display labels, you must specify a return clause, use the RETURNING keyword. The return clause in the following figure specifies that the routine will return an INT value with a serial_num display label, a CHAR value with a name display label, and an INT value with a points display label. You could use either CREATE FUNCTION or CREATE PROCEDURE in the following figure.

Figure 1. Specify the return clause.

CREATE FUNCTION p(inval INT DEFAULT 0)
   RETURNING INT AS serial_num, CHAR (10) AS name, INT AS points;
   RETURN (inval + 1002), "Newton", 100;
END FUNCTION;

The returned values and their display labels are shown in the following figure.

Figure 2. Returned values and their display labels.

serial_num    name      points

1002          Newton    100

Tip: Because you can specify display labels for return values directly in a SELECT statement, when a SPL routine is used in a SELECT statement, the labels will display as expression. For more information on specifying display labels for return values in a SELECT statement, see Compose SELECT statements.