Storing result set as a variable in a procedure

Database

Storing result set as a variable in a procedure

Hi,

I am trying to pass a result set of a query into a procedure. I  only need one column from a table but the column will have multiple rows.

I have tried within the procedure to have : SELECT columnname INTO :variable from dbc.tablename where columnname2 = 'value';

I need all the rows from the result so that I can pass it into another query within the procedure, is there any way to do this or an alternative.

The current error I get is:  CALL Failed. 7627:  SELECT-INTO returned more than one row. 

Thanks,

Akhil

7 REPLIES
Enthusiast

Re: Storing result set as a variable in a procedure

A variable can only store one value at a time, not multiple values. You can consider using cursor in SP to get recordset and pass it thru loop one by one.

Re: Storing result set as a variable in a procedure

Hi,

Thank you for your reply. I have got a cursor working, but currently it does it stop at the end of the result set. It ends up stuck in the loop and repeatedly processes the last row of the column until I cancel the query.

I know the part 'sqlcode=0' may not be the most appropriate but any ideas what could replace this. I was thinking about doing a SELECT COUNT(*) from table1 and somehow passing it into the while loop but not sure how.

Another question I have is, instead of the INSERT statement, I want to concatenate all the rows of the column into the following format: 'row1','row2','row3' etc.

I tried the following: SET param3 = '' param3 ||',' field1 '';    with the intention that every time the loop goes around, the new row will be added to the end of param3, but this gives me the error 'Illegal attempt to modify symbol 'param3'.'

This is all because I need to pass the combination of the rows as one string, as a variable, into a function.

I have pasted the code below. Thanks!

REPLACE PROCEDURE test (IN param1 VARCHAR(200), INOUT param2 VARCHAR(200), OUT param3 VARCHAR (200))

BEGIN 

DECLARE field1 VARCHAR(200);

DECLARE my_cursor CURSOR FOR

   SELECT column1 FROM table1 WHERE column2 = :param1;

   OPEN my_cursor;

   MainLoop:WHILE (sqlcode=0) DO

        FETCH  my_cursor INTO field1, param2;

        INSERT INTO table2 VALUES (:field1, :param2);

   END WHILE MainLoop;

  CLOSE my_cursor;

END;




Fan

Re: Storing result set as a variable in a procedure

The loop is caused by the logic you have used. 

You are checking sqlcode, as you should, but you are checking the result of the insert not the fetch.

Perform a single fetch before MainLoop, and move the insert to above the Fetch. That should stop the loop.

OPEN my_cursor;

FETCH  my_cursor INTO field1, param2;

   MainLoop:WHILE (sqlcode=0) DO

        INSERT INTO table2 VALUES (:field1, :param2);

        FETCH  my_cursor INTO field1, param2;

   END WHILE MainLoop;

  CLOSE my_cursor;

Re: Storing result set as a variable in a procedure

hi,

need a small stored procedure.

i have a table "ABC" with 2 filed "no and counts".

if no is 1 and counts is 10

thani want to insert 10 rows in second table against this no.

if no is 2 and counts is 5

than i want to insert 5 rows in second table agains this no.

any help ????

Re: Storing result set as a variable in a procedure

Hi John,

Thank you for your reply, I forgot to mention it worked.

I have a second question you may be able to help on, with the cursor I created, I pass the variable into an SQL statement. The statement is actually a function that is generated, with the variable holding some user ID's I need to pass into it.

Is it possible to run this function, from within the procedure once generated? Is this because there is a limitation to what can be run from within a procedure?

The following do not seem to work;

CALL dbc.sysexecsql(:SqlTxt);

EXECUTE IMMEDIATE SqlTxt;

Enthusiast

Re: Storing result set as a variable in a procedure

Can you help me to store the list of column name in a variable (as tablename will be parameterized) and use it later in Stored procedure in the insert and select statement? Is it possible without using cursor?

thanks




Enthusiast

Re: Storing result set as a variable in a procedure

Hi Burhan,

What are the values you are going to insert in second table? I mean if you say 5 count, 10 counts...then you will insert from 1 to 5 & 1 to 10 or some other values?

To answer to your question, you can try this way:

select column2 from abc where column1 = <value>;

Based on the column2 resultset you can insert the number of records in the second table.