IDS Forum

INOUT parameters in stored procedure

Posted By: MARK COLLINS
Date: Tuesday, 23 June 2009, at 2:38 p.m.

I may be misunderstanding how INOUT parameters are supposed to be used, so please correct me if that is the case.

I have a procedure proc_a that needs to populate several procedure variables, and I was hoping to populate these by using another procedure proc_b. Take the following snippet from proc_a:

create procedure proc_a()

define form_nbr smallint;
define question_nbr smallint;
define rsp1_desc varchar(80);
define rsp2_desc varchar(80);
define rsp3_desc varchar(80);
define rsp4_desc varchar(80);

let form_nbr = 4;
let question_nbr = 1;
call proc_b(form_nbr, question_nbr, rsp1_desc, rsp2_desc, rsp3_desc, rsp4_desc);

trace rsp1_desc;
trace rsp2_desc;
trace rsp3_desc;
trace rsp4_desc;
.
.
.

And here is proc_b:

create procedure test_question(i_form_nbr smallint

, i_question_nbr smallint

, inout io_rsp1_desc varchar

, inout io_rsp2_desc varchar

, inout io_rsp3_desc varchar

, inout io_rsp4_desc varchar)

select r1.response_desc1, r1.response_desc2, r1.response_desc3, r1.response_desc4
into io_rsp1_desc, io_rsp2_desc, io_rsp3_desc, io_rsp4_desc
from survey_questions q
left outer join survey_response r1 on r1.survey_form_nbr = q.survey_form_nbr and r1.surv_question_nbr = q.surv_question_nbr
where q.survey_form_nbr = i_form_nbr and q.surv_question_nbr = i_question_nbr;
.
.
.

Since the manual states that INOUT parameters are passed by reference, I had hoped that when proc_b was executed, the values that it SELECTed into the io_*_desc fields would end up populating the variables ques_desc and rsp[1-4]_desc in proc_a.

However, when I run proc_a, I get error "-9752 Argument must be a Statement Local Variable for an OUT/INOUT parameter." The manual indicates that SLVs are only used in the WHERE clause, not in the CALL statement. I tried using SLVs in the CALL statement and got a different error. I tried putting an SLV in the WHERE clause (where q.survey_form_nbr = i_form_nbr # smallint), but that caused a "201: A syntax error has occurred."

Messages In This Thread

• INOUT parameters in stored procedure
  MARK COLLINS -- Tuesday, 23 June 2009, at 2:38 p.m.
  • Re: INOUT parameters in stored procedure
    Art Kagel -- Tuesday, 23 June 2009, at 7:10 p.m.
    • Re: INOUT parameters in stored procedure
      MARK COLLINS -- Tuesday, 23 June 2009, at 10:14 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.