Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2010
    Posts
    2

    Unanswered: DB2 Stored Procedure to return multiple rows

    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.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Mar 2010
    Posts
    2
    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.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •