By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,425 Members | 2,549 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,425 IT Pros & Developers. It's quick & easy.

SQL0469 on DB2 stored procedure CALL

P: 1
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 !!
Dec 28 '07 #1
Share this Question
Share on Google+
2 Replies


sakumar9
Expert 100+
P: 127
I am not sure if you tried following command:

Expand|Select|Wrap|Line Numbers
  1. CALL Stored_Procedure('asasas', 'aaaaa', ?, ?)
  2.  
We need to give the input parameters as values and output values as '?'.

Let me know if you still have problems. Thanks..


Regards
-- Sanjay
Feb 21 '08 #2

P: 1
I recomend you yo check out this link:

http://www.experts-exchange.com/Data..._21827727.html

Here it is a possible solution to your problem.
Apr 18 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.