I created the following stored procedure:
=======
CREATE PROCEDURE TBLNAME.proc_test (IN p_custnum VARCHAR(8),
IN p_zipcode CHAR(5),
OUT r_valid CHAR(1),
OUT r_bal DECIMAL(9,2))
LANGUAGE SQL
BEGIN
[etc..]
=======
The procedure has been created successfuly. ("Statement ran successfully")
Now to give it a try, I use the CALL statement. I found out that if you don't call the stored procedure with exactly the same number as parameters (including the OUT parameters), it won't work. So what I usually do is to call the procedure using NULL as parameter for the OUT parameters. But on this one, it just won't work:
CALL TBLNAME.proc_test('12345678','12345',NULL,NULL)
[SQL0469] IN, OUT, or INOUT not valid for parameter 3 in procedure PROC_TEST in IVRCIS. Cause . . . . . : The IN, INOUT, or OUT attribute specified for parameter 3 on the DECLARE PROCEDURE or CREATE PROCEDURE statement is not valid. The parameter name is R_VALID. One of the following errors occurred: -- The attribute is not consistent with the parameter on the CALL statement. If the parameter was declared INOUT or OUT, the parameter on the CALL statement must be specified as a host variable. -- The attribute was specified as INOUT or OUT and REXX was specified as the language. The attribute must be IN if REXX is specified. -- A parameter in an SQL procedure is declared as OUT and is used as input in the routine body or is declared as IN and is modified in the routine body. -- A parameter in an SQL function is modified in the routine body. Recovery . . . : Either change the attribute of the parameter on the DECLARE PROCEDURE or CREATE PROCEDURE statement or change the parameter. Do not modify parameters in an SQL function. Try the request again.
I tried pretty much all combinations of call, including:
CALL TBLNAME.proc_test('12345678','12345',NULL,NULL)
CALL TBLNAME.proc_test('12345678','12345','a',1)
CALL TBLNAME.proc_test('12345678','12345',?,?)
CALL TBLNAME.proc_test('12345678','12345')
and I am not able to run the stored procedure...
Anyone can help ?
I'm using the "Run SQL Scripts" GUI from iSeries Navigator v5R2.
Thanks !!