Parametrized VIEW or STORED PROCEDURE returning data set

frun · #1 (**permalink**) 05-05-09, 10:35

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?

ARWinner · #2 (**permalink**) 05-05-09, 10:49

What DB2 version and OS?

Andy

frun · #3 (**permalink**) 05-05-09, 11:10

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.

tonkuma · #4 (**permalink**) 05-05-09, 11:16

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
;

ARWinner · #5 (**permalink**) 05-05-09, 11:17

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

frun · #6 (**permalink**) 05-05-09, 11:24

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.

frun · #7 (**permalink**) 05-06-09, 09:38

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?

ARWinner · #8 (**permalink**) 05-06-09, 09:44

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

rdutton · #9 (**permalink**) 05-06-09, 10:09

The syntax is CLOSE CURSOR1;

ARWinner · #10 (**permalink**) 05-06-09, 10:15

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

rdutton · #11 (**permalink**) 05-06-09, 10:31

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 .