Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2009
    Posts
    21

    Question Unanswered: Parametrized VIEW or STORED PROCEDURE returning data set

    I'm newbie in DB2 and i'm not able to realize following task.

    I need view or stored procedure with parameters to return folowing SELECT from the database

    SELECT col1, col2 FROM AAA
    WHERE aValidFrom <= PAR1 AND aValidTo >= PAR2
    AND ID NOT IN (SELECT ID FROM BBB WHERE bValidFrom <= PAR1 AND bValidTo >= PAR2)

    The problem is i don't know
    - how to return data set (SELECT) from stored procedure (if possible)
    and / or
    - how to pass parameters PAR1 and PAR2 to the view (if possible)

    Does anybody have a solution, please?
    Last edited by frun; 05-05-09 at 11:41.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What DB2 version and OS?

    Andy

  3. #3
    Join Date
    Apr 2009
    Posts
    21
    DB2 v9.5.0.808 (Express), OS Win 2003 Server

    Sorry, I forgot it again. I have feeling the SQL should be version and OS independent.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another idea might be a table UDF.

    Change <data type 1>, <data type 2>, <data type 3> and <data type 4> with approprate data types.
    Code:
    CREATE FUNCTION udf_name(par1 <data type 1>, par2 <data type 2>)
    RETURNS TABLE (col1 <data type 3>, col2 <data type 4>)
    READS SQL DATA
    DETERMINISTIC
    NO EXTERNAL ACTION
    RETUEN
    SELECT col1, col2 FROM AAA
    WHERE aValidFrom <= PAR1 AND aValidTo >= PAR2
    AND ID NOT IN (SELECT ID FROM BBB WHERE bValidFrom <= PAR1 AND bValidTo >= PAR2)
    ;
    Code:
    SELECT col1, col2
      FROM TABLE( udf_name(xxxxx, yyyyy) ) q
    ;

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Not for DB2 for LUW. There maybe newer features in newer versions, but the syntax in older version usually works in the newer.

    What you need is something like this:

    create procedure Myschema.MyProcedure(IN par1, IN par2)
    BEGIN
    DECLARE CURSOR1 CURSOR WITH RETURN TO CALLER FOR
    SELECT col1, col2 FROM AAA
    WHERE aValidFrom <= PAR1 AND aValidTo >= PAR2
    AND ID NOT IN (SELECT ID FROM BBB WHERE bValidFrom <= PAR1 AND bValidTo >= PAR2) ;

    OPEN CURSOR1; -- cursor left open for returning result set
    END @

    Look in the manual for the entire syntax for the create procedure statement.

    Andy

  6. #6
    Join Date
    Apr 2009
    Posts
    21
    Quote Originally Posted by tonkuma
    Another idea might be a table UDF.

    Change <data type 1>, <data type 2>, <data type 3> and <data type 4> with approprate data types.
    Code:
    CREATE FUNCTION udf_name(par1 <data type 1>, par2 <data type 2>)
    RETURNS TABLE (col1 <data type 3>, col2 <data type 4>)
    READS SQL DATA
    DETERMINISTIC
    NO EXTERNAL ACTION
    RETUEN
    SELECT col1, col2 FROM AAA
    WHERE aValidFrom <= PAR1 AND aValidTo >= PAR2
    AND ID NOT IN (SELECT ID FROM BBB WHERE bValidFrom <= PAR1 AND bValidTo >= PAR2)
    ;
    Code:
    SELECT col1, col2
      FROM TABLE( udf_name(xxxxx, yyyyy) ) q
    ;
    It seems good, but I'm affraid FUNCTION can't be mapped using LINQ to DB2 provider that I'm using from my .NET code.

  7. #7
    Join Date
    Apr 2009
    Posts
    21
    Quote Originally Posted by ARWinner
    What you need is something like this:

    create procedure
    ...
    OPEN CURSOR1; -- cursor left open for returning result set
    END @

    Andy
    Thank you, it works, but isn't it any resource leak to leave cursor opened? Or should I close it somehow?

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I believe that the cursor gets closed when you have read all the rows from it.
    There is no way to close it explicitly with SQL.

    Andy

  9. #9
    Join Date
    Dec 2008
    Posts
    76
    The syntax is CLOSE CURSOR1;

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by rdutton
    The syntax is CLOSE CURSOR1;
    This will not work. The cursor is declared in the stored procedure. You cannot see it or reference its name. If you call the SP and then try to close the cursor like this you will get the error:

    DB21028E The cursor "CURSOR1" has not been declared.

    Andy

  11. #11
    Join Date
    Dec 2008
    Posts
    76
    Sorry - didn't look closely enough at the previous posts, didn't notice the declaration WITH RETURN TO CALLER. Responded to the statement out of context .
    Last edited by rdutton; 05-06-09 at 11:34.

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by frun
    Thank you, it works, but isn't it any resource leak to leave cursor opened? Or should I close it somehow?
    The cursor will be closed upon the next COMMIT.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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