All Forums Database
j355ga 100 posts Joined 12/05
12 Jul 2013
no data condition with dynamic result set


Anybody know how to detect NO DATA condition for a select in dynamic result set?  


This simple example detects the no condition  for the UPDATE but  the select does not generate a no data condition.






replace  PROCEDURE   get_id ( in  p_id char(7) )

dynamic result sets 2




declare v_sql_sel varchar(2000);

declare v_sql_upd varchar(2000);

declare v_test integer default 1;


declare emp_hdr_sel cursor with return only  for s1;

declare emp_hdr_upd cursor with return only  for u1;


declare exit handler for not found insert into    sp_log values(current_timestamp, v_test, 'There was no data');


set v_sql_sel =  'select lname from  emp where id=?;';


set v_sql_upd =  'update   emp set lname=''FOO'' where id = ? ; ';


prepare s1 from v_sql_sel;

open emp_hdr_sel using p_id;


 set v_test =2;


prepare u1 from v_sql_upd;

open emp_hdr_upd using p_id;




dnoeth 4628 posts Joined 11/04
13 Jul 2013

Hi Jeff,
i don't know if it's possible to use a NOT FOUND handler for a dynamic cursor, but your 2nd cursor should never work as an UPDATE can't be used for OPEN, you have to use Dynamic SQL instead.
What happens when you remove the handler or change it to CONTINUE instead of EXIT?


j355ga 100 posts Joined 12/05
15 Jul 2013

Did some testing and the NOT FOUND handler does not trigger for a dynamic result set.
When the NOT FOUND handler is removed then the update returns a run time error: invalid statement specified in a dynamic declare cursor.
Seems counter intuitive that NOT FOUND is triggered for run-time errors.  The manual says "NOT FOUND is a generic condition that represents the SQLSTATE codes for all 'no data
found' completion conditions." 


You must sign in to leave a comment.