SQL Language Guide : 7. Database Procedures, Sessions, and Events : How Database Procedures Are Created, Invoked, and Executed : How Parameters Are Passed in Database Procedures
 
Share this page                  
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 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);