DB2 Stored Procedure to return multiple rows

miketest · #1 (**permalink**) 03-24-10, 15:13

I am a newbie to stored procedure. I need to write a simple SQL stored procedure in DB2 to select from one table and return multiple rows. Here is the SQL that I have today.

select col1,col2,open_date
from table1
where col1 IN (123,456,789)
and col2 IN (7878, 5656)

I can pass the values for col1 and col2 when I call the SP. But, it can return multiple rows. I would like to know how to get the multiple rows in my calling program.

I have searched this program and went thru the redbook (Through the Call and Beyond). I couldnt find any example. All it says is I need to declare and open the cursor but it doesnt say how to get multiple rows in the calling program.

Can someone give an example for this? Your help is appreciated. Thanks.

Mike.

ARWinner · #2 (**permalink**) 03-24-10, 15:36

Your SP will look like this:

Code:

CREATE PROCEDURE MySchema.MySP (IN parm1 int)
SPECIFIC MySchema.MySP 
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
     DECLARE CURSOR1 CURSOR WITH RETURN TO CALLER FOR
select col1,col2,open_date
from table1
where col1 IN (123,456,789)
and col2 IN (7878, 5656)
;

   OPEN CURSOR1;

How you call it and retrieve the result set depends on the language you are using to develop the application.

Andy

miketest · #3 (**permalink**) 03-25-10, 16:00

Thanks for the reply. I am going to be calling the SP from Mercator map and I am not sure how this is going to work.

Does your solution involve calling the SP more than once? I dont want that. I would like to call the SP only once and get the result set. Is there a way to do it?

Also, assuming if I am calling from Java, how do I get the result set from the DECLARE/OPEN cursor you have mentioned. Thanks.

Mike.

sathyaram_s · #4 (**permalink**) 03-25-10, 16:07

I think what andy has given is an example for you to take and develop on ... And, you have given very little information to enable anyone to give a definitive solution.

For starters, you have to mention the db2 platform and version.

sathyaram_s · #5 (**permalink**) 03-25-10, 16:13

How to use JDBC and Stored Proc resultset

Stored procedure with Input/Output parms and a ResultSet - Real's Java How-to

ARWinner · #6 (**permalink**) 03-25-10, 16:18

I do not know what Mercator Map is.

No, you should only call the SP once to get the entire result set. Try it by using the CLP and see what you get.

To use java, it would look something like:

Code:

java.sql.CallableStatement cstmt;
java.sql.ResultSet rs;
int parm1;
...

cstmt = dbConnection.prepareCall('{call myschema.mysp(?)}');
cstmt.setInt(1,parm1);
rs = cstmt.executeQuery();
while (rs.next()) do
{
  // process entire result set here
}
rs.close();

Andy

Internet Services

Web Development

Digital Marketing

Consumer Tech