how to make Stored Procedure with dynamic SQL to display the records?

Database
Enthusiast

how to make Stored Procedure with dynamic SQL to display the records?

Can anyone please help? Below code can finish running with no error, but how to make it to display the records, just so I know I'm getting what I want. Thanks!

replace PROCEDURE dlcna_cateam.sp_test3 

(

IN SRC_DB_NM VARCHAR(30)

, IN SRC_TBL_NM VARCHAR(30)

)

DYNAMIC RESULT SETS 1

BEGIN

DECLARE QUERY1 VARCHAR(4000);

DECLARE cur1 CURSOR FOR S1;

BEGIN  

SET QUERY1 = 'select * from ' || SRC_DB_NM || '.' || SRC_TBL_NM || ';';

     BEGIN

     PREPARE S1 FROM QUERY1;

     OPEN cur1;

     END;    

END;

END;

call sp_test3('dlcna_cateam','NEWMOM_LCL_TRAN');

3 REPLIES
Senior Apprentice

Re: how to make Stored Procedure with dynamic SQL to display the records?

You forgot to add WITH RETURN ONLY to the cursor definition:

DECLARE cur1 CURSOR WITH RETURN ONLY FOR S1;
Enthusiast

Re: how to make Stored Procedure with dynamic SQL to display the records?

thanks a lot Dieter!!!

Enthusiast

Re: how to make Stored Procedure with dynamic SQL to display the records?

Hi Dieter,



 I have data in one of the table as below

 col1 col2   begindate     enddate

 A     100  '2016-01-01' '2016-02-18'

 A     100  '2016-03-01' '2016-04-07'

 A     101  '2016-03-31' '2016-04-20'

 A     100  '2016-05-01' '2016-06-03'

 A     100  '2016-06-18' '2016-07-07'

 i need output like

 A 100 '2016-01-01' '2016-04-07'

 A 101 '2016-03-31' '2016-04-20'

 A 100 '2016-05-01' '2016-07-07'

 Basically i need minium begin date and maximum of end date based on col1 & col2 but only when col2 is continuous.

 Any help would be highly helpful.

 Thanks,

 Hanu