All Forums UDA
Sanky 4 posts Joined 07/07
02 Jul 2007
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;ENDWhen 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

joedsilva 505 posts Joined 07/05
03 Jul 2007

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).

Sanky 4 posts Joined 07/07
03 Jul 2007

Hi Silva,Thank you for this input.Very well appreciated.Thanks !

Sanky 4 posts Joined 07/07
03 Jul 2007

Hi Silva,I'm getting the following error message when I use Prepare.CREATE ()BEGINDECLARE 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 ?

joedsilva 505 posts Joined 07/05
03 Jul 2007

AFAIK, The prepare statement syntax is used for embedded SQL applications..ok this is what I had in mind ...., I haven't tested the syntaxREPLACE 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 recordsCALL 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 ;

You must sign in to leave a comment.