IDS Forum

Re: INOUT parameters in stored procedure

Posted By: Art Kagel
Date: Tuesday, 23 June 2009, at 7:10 p.m.

In Response To: INOUT parameters in stored procedure (MARK COLLINS)

To pass args back from a procedure just use RETURNING. So prob_b is defined
as:

create function

returning varchar as io_rsp1_des, varchar as io_rsp2_des, varchar as
io_rsp3_des, varchar as io_rsp4_des,;

define lcl_rsp1_desc, lcl_rsp2_desc, lcl_rsp3_desc, lcl_rsp4_desc varchar;

...
select r1.response_desc1, r1.response_desc2, r1.response_desc3,
r1.response_desc4
into lcl_rsp1_desc, lcl_rsp2_desc, lcl_rsp3_desc, lcl_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;

...

return lcl_rsp1_desc, lcl_rsp2_desc, lcl_rsp3_desc, lcl_rsp4_desc;

end function;

Then proc_a becomes:

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;

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

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

Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.

On Tue, Jun 23, 2009 at 2:38 PM, MARK COLLINS <markc@myfastmail.com> wrote:

> 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."
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--001636c5a8d1b0d74f046d0c1802

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.