How Parameters Are Passed in Database Procedures
By default, the EXECUTE PROCEDURE statement passes parameters to a database procedure by value. To pass a value by reference, use the BYREF option. If a parameter is passed by reference, the called database procedure can change the contents of the variable, and the change is visible to the calling program.
In addition to the BYREF option for passing parameters from an invoking application, parameters can be passed in one of three declared modes: IN, OUT, and INOUT. IN is the default parameter mode and is equivalent to the by value mode described above. It cannot return values to the calling procedure, rule, or application. The OUT and INOUT modes allow the passing of possibly modified parameter values back to the calling database procedure or triggering operation, for example, allowing the application to modify a column value in a row before it is inserted or updated.
Example:
CREATE PROCEDURE getnamezip (IN custno int NOT NULL, OUT custname, OUT custzip)
AS
DECLARE
p_errnum INTEGER NOT NULL NOT DEFAULT;
p_rowcount INTEGER NOT NULL NOT DEFAULT;
BEGIN
...
SELECT c_name,
c_zip
INTO :custname,
:custzip
FROM customer
WHERE c_id = :custno;
...
END;
EXECUTE PROCEDURE getnamezip (custno = :w_custno,
custname = :w_custname,
custzip = :w_custzip);
Last modified date: 08/29/2024