Dynamic WHERE clause in Cursor

UDA
N/A

Dynamic WHERE clause in Cursor

Hi,

Can I DECLARE a Cursor with dynamic WHERE clause in Stored Procedure ?
So that I can re-use this Stored procedure.

Please see the example below ?

CREATE Procedure SP_TEST(IN vMetric VARCHAR(20))
BEGIN
DECLARE Test_Cursor CURSOR FOR
SELECT Name, Basic_salary, Bonus, Tax
FROM Employee
WHERE :vMetric > 0;
END

When I can this SP I will call with columns names as

- call SP_TEST ('Basic_salary')
Cursor should evaluate the SELECT statement as
SELECT Name, Basic_salary, Bonus, Tax
FROM Employee
WHERE Basic_salary > 0;

- call SP_TEST ('Bonus')
Cursor should evaluate the SELECT statement as
SELECT Name, Basic_salary, Bonus, Tax
FROM Employee
WHERE Bonus > 0;

- call SP_TEST ('Tax')
Cursor should evaluate the SELECT statement as
SELECT Name, Basic_salary, Bonus, Tax
FROM Employee
WHERE Tax > 0;

Any help would be greatly appreciated.

Thanks,
Sanky
4 REPLIES

Re: Dynamic WHERE clause in Cursor

you can't do it that way.

what you need is an SP, that will generate a dynamic SQL statement and execute it.

the dynamic SQL will have to insert/select the result sets into a temporary table (use a volatile/global temporary table)

then inside the SP, you can build a cursor to fetch from it (the temporary table to which the dynamic SQL inserted records).
N/A

Re: Dynamic WHERE clause in Cursor

Hi Silva,

Thank you for this input.
Very well appreciated.

Thanks !
N/A

Re: Dynamic WHERE clause in Cursor

Hi Silva,

I'm getting the following error message when I use Prepare.

CREATE ()
BEGIN
DECLARE vStatement_Name varchar(18) ;
DECLARE vSQL_Statement varchar(200) ;

SET vSQL_Statement = 'INSERT into EDW_TEMPd.RL_AGRGT_SL_STG_TEMP SELECT * from EDW_TEMPd.RL_AGRGT_SL_STG_TEMP' ;

PREPARE vStatement_Name FROM :vSQL_Statement;

END ;

SPL1027:E(L104), Missing/Invalid SQL statement'E(3707):Syntax error, expected something like an 'END' keyword between ';' and the 'PREPARE' keyword.

Any help ?

Re: Dynamic WHERE clause in Cursor

AFAIK, The prepare statement syntax is used for embedded SQL applications.
.

ok this is what I had in mind ...., I haven't tested the syntax

REPLACE PROCEDURE SP_TEST
(
IN vMetric VARCHAR(30)
)
BEGIN

-- This line may not be required .....
DELETE FROM MYDB.TEMP_EMP_TBL ALL;

-- Populate the table with required records
CALL DBC.SYSEXECSQL('INSERT INTO MYDB.TEMP_EMP_TBL SELECT Name, Basic_salary, Bonus, Tax FROM Employee WHERE '|| :vMetric || ' > 0 ;' );

-- Write the code to fetch from MYDB.TEMP_EMP_TBL here ...

END ;