Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2013
    Posts
    2

    Unanswered: Conditional return using Cursors

    I am new to DB2, and writing stored procedures, so I'm looking for help. I'm sure this has been asked and answered many times, and I've probably seen the answer already, but being so new to this stuff, didn't realize I was looking at my answer. I'm writing a stored procedure, and we use Cursors at my company, so the stored procedure must be written using Cursors. This is basically what I would like the stored procedure to do:

    IN var1 NUMERIC(9),
    IN var2 NUMERIC(9),
    IN var3 CHARACTER(2)

    DECLARE RESULTSET1 CURSOR WITH RETURN FOR
    WITH subquery1 as (
    SELECT * FROM table1
    WHERE field1 = var1)

    SELECT * FROM subquery1
    WHERE field2 = var2;

    this is where I need help
    If RESULTSET1 returned results great OPEN RESULTSET1; END;
    BUT, if RESULTSET1 is empty/null/returned no results run this query


    SELECT * FROM subquery1
    WHERE field2 = var3;



    Any help would be greatly appreciated.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You can not know if if the query returns anything until you attempt to FETCH from its cursor, at which point the result set is likely of no use to the procedure caller.

    If you rewrite your select statement to something like
    Code:
    WITH subquery1 as (
     SELECT * FROM table1
     WHERE field1 = var1)
    
    SELECT * FROM subquery1
    WHERE field2 = var2
    OR (field2 = var3 AND NOT EXISTS (
      SELECT 1 FROM subquery1
      WHERE field2 = var2)
    )
    it will do more or less what you want.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Sep 2013
    Posts
    2
    Ok, that makes sense, I will give it a try. Thanks!

    i was starting to think of just writing 2 separate stored procedures and do some of the logic outside of the stored procedure something like:

    Code:
    $results = results from stored procedure 1;
    
    if($results == ''):
        $results = results from stored procedure 2;
    endif;

Posting Permissions

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