Dynamic Cursor in DB2 Stored Procedure

[a***@fiserv.com]

Ok, here it is

I need define a cursor having select statement with dynamic where clause.

Here is the detailed requirement in points.

1. Create a stored procedure with parameters which will be part of where clause of cursor in procedure definition/body.
2. This SP will have the cursor a select statement with dynamic where clause.
3. This stored procedure will be executed from a .bat file with parameters.


Please let me know how do I do this in DB2.

Sample code would be of great help

[a***@fiserv.com]

Hi Guys,
Please let me know if the query is not clear.

I belive what I am asking is not the Rocket Science.

I need to construct SELECT STATEMENT on the FLY and process the same in CURSOR.

I need something like the code below
**************************************************************************
MOVE ?SELECT EMPNO, LASTNAME FROM CORPDATA.EMPLOYEE WHERE EMPNO>??
TO DSTRING.
EXEC SQL PREPARE S2 FROM :DSTRING END-EXEC.

EXEC SQL DECLARE C2 CURSOR FOR S2 END-EXEC.

EXEC SQL OPEN C2 USING :EMP END-EXEC. PERFORM FETCH-ROW UNTIL SQLCODE NOT=0.

EXEC SQL CLOSE C2 END-EXEC.
STOP-RUN.
FETCH-ROW.
EXEC SQL
FETCH C2 INTO :EMP, :EMPNAME END-EXEC.
*************************************************************************

I was unable to execute the above code in DB2 Command Centre.

PLEASE HELP!!

[a***@fiserv.com]

OK...Guys, I was able to resolve the problem
*******************************************************************************
CREATE PROCEDURE "DB2ADMIN"."TESTP" (IN vWhere varchar(20), IN vValue varchar(30) )

RESULT SETS 1
LANGUAGE SQL

BEGIN

DECLARE v_table_count VARCHAR(200);
DECLARE v_sql VARCHAR(200);
DECLARE v_WhrClause VARCHAR(200);
DECLARE v_deg VARCHAR(200);
DECLARE v_stmt statement;

declare c2 cursor for v_stmt;

set v_WhrClause = vWhere || ' = ' || ''''|| vValue ||'''';
set v_deg = 'SELECT LASTNAME FROM DB2ADMIN.EMPLOYEE WHERE ' || v_WhrClause ;
set v_sql='SELECT LASTNAME FROM DB2ADMIN.EMPLOYEE WHERE ' || v_WhrClause ;
prepare v_stmt from v_sql;
open c2;
fetch c2 into v_table_count;
close c2;

END
*******************************************************************************