Stored procedures with output parameters

Genero db stored procedures must be called with the input and output parameters specification in the USING clause of the EXECUTE, OPEN or FOREACH instruction. As in normal dynamic SQL, parameters must correspond by position, and the IN/OUT/INOUT options must match the parameter definition of the stored procedure.

To execute the stored procedure, you must use the CALL SQL instruction:

PREPARE stmt FROM "call proc1(?,?,?)"

Here is a complete example creating and calling a stored procedure:

MAIN
   DEFINE n INTEGER
   DEFINE d DECIMAL(6,2)
   DEFINE c VARCHAR(200)
   DATABASE test1
   EXECUTE IMMEDIATE
                   "create procedure proc1("
                || "    p1 in int,"
                || "    p2 out number(6,2), "
                || "    p3 in out varchar2"
                || "    )"
                || " is begin"
                || "  p2:= p1 + 0.23;"
                || "  p3:= 'Value = ' || p1;"
                || "end;"
   PREPARE stmt FROM "call proc1(?,?,?)"
   LET n = 111
   EXECUTE stmt USING n IN, d OUT, c INOUT
   DISPLAY d
   DISPLAY c
END MAIN