INOUT parameters in stored procedure

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."

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

I had considered that, and may have to resort to that. The reason that I wanted to use the INOUT is because the number of responses varies -- sometimes there are three responses, sometimes four, sometimes six, etc. Doing it with a RETURNING clause means that I have to code n separate procedures and write the code to call the correct procedure. I was wanting to use function overloading and just pass the parameters in the call, and have the engine figure out which of the overloaded versions of the procedure to call. Granted, I still have to write n procedures with this method, but now the call would be identical (other than the arg list). Plus, it looks cool. All kidding aside, it looked like an opportunity to try something new.