Dynamic Cursor

Database
Enthusiast

Dynamic Cursor

Hi i am trying to execute dynamic sql in stored procedure and returning as a cursor. Can somebody
help me to correct below stored procedure. My sql will be much complicated then this one , currently I am using simple one.

REPLACE PROCEDURE pp_get_myCustomers(
user_logged integer )
DYNAMIC RESULT SETS 1
BEGIN

DECLARE dyn_sql VARCHAR(10000);

SET dyn_sql = 'Select top 10 * from CUSTOMER;' ;

DECLARE cur1 CURSOR WITH RETURN ONLY FOR

CALL DBC.SysExecSQL(: dyn_sql);

OPEN cur1;

END;

Error Details :
SPL1027:E(L23), Missing/Invalid SQL statement'E(3707):Syntax error, expected something like an 'END' keyword between ';' and the 'DECLARE' keyword.'.
SPL1030:E(L25), Referring to undefined cursor 'cur1'.
2 REPLIES

Re: Dynamic Cursor

Please find the Sample code: which uses the Query from the IN Parameter:

REPLACE PROCEDURE spSample2(IN S2 VARCHAR(1000),OUT Pcount DECIMAL(18,0))
--READS SQL DATA
DYNAMIC RESULT SETS 1

BEGIN

DECLARE MyCur CURSOR WITH RETURN ONLY TO CLIENT FOR FirstStatement;

PREPARE FirstStatement FROM S2;

open MyCur;

SET Pcount = ACTIVITY_COUNT;
close MyCur;
END;

hope this would be helpful.....
Fan

Re: Dynamic Cursor

Hi I am using the same.
My Query contains unicode character eg. select * from CUSTOMER where column1 = 'MÜNCHEN'
I am unable to get the desired output.

Kindly Help solve the issue