Stored procedures with output parameters

IBM^® DB2^® 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 with output parameters:

MAIN
   DEFINE n INTEGER
   DEFINE d DECIMAL(6,2)
   DEFINE c VARCHAR(200)
   DATABASE test1
   EXECUTE IMMEDIATE
                   "create procedure proc1("
                || "          in p1 int,"
                || "          out p2 decimal(6,2),"
                || "         inout p3 varchar(20)"
                || "        )"
                || " language sql begin"
                || "  set p2 = p1 + 0.23;"
                || "  set p3 = 'Value = ' || char(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