Stored functions returning values

The traditional way to return values from an IBM^® Informix^® SPL routine is to fetch the output values, as from a regular SELECT producing a result set.

You must define a stored function with a RETURNING clause. IBM Informix stored procedures do not return values.

To execute a stored function with IBM Informix, you must use the EXECUTE FUNCTION SQL instruction:

PREPARE stmt FROM "execute function proc1(?)"

In order to retrieve returning values into program variables, you must use an INTO clause in the EXECUTE instruction.

The following example shows how to call a stored function with IBM Informix:

MAIN
   DEFINE n INTEGER
   DEFINE d DECIMAL(6,2)
   DEFINE c VARCHAR(200)
   DATABASE test1
   EXECUTE IMMEDIATE "create function proc1( p1 integer )"
                || " returning decimal(6,2), varchar(200);"
                || "  define p2 decimal(6,2);"
                || "  define p3 varchar(200);"
                || "  let p2 = p1 + 0.23;"
                || "  let p3 = 'Value = ' || p1;"
                || "  return p2, p3;"
                || " end function;"
   PREPARE stmt FROM "execute function proc1(?)"
   LET n = 111
   EXECUTE stmt USING n INTO d, c
   DISPLAY d
   DISPLAY c
END MAIN