Reputation: 499
I am looping over a cursor and have the standard exist loop control set however in the loop I also do select into from another table, if this search returns no results then that triggers the exit handler and the loop terminates.
My question is: How do I attached the continue handler for the loop to a cursor and ignore the select into or how do I get round this problem?
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_results=1;
.
.
.
search_loop:WHILE (no_more_results=0) DO
.
FETCH my_csr INTO something;
.
SELECT thing INTO my_thing FROM `dups` where `dups`.thing = thing_id;
The last select triggers the exit conditions...
Thanks Paul
Upvotes: 1
Views: 742
Reputation: 425448
Since your code is expecting zero or one rows, use the SET
syntax:
SET my_thing = (SELECT thing FROM dups where dups.thing = thing_id);
This will set my_thing
to null
if there are no rows.
I haven't tested this, so if it is still setting the handler, change to this, which always returns a row:
SET my_thing = SELECT IFNULL((SELECT thing FROM dups where dups.thing = thing_id), null);
Alternatively, you can convert your statement similarly:
SELECT IFNULL((SELECT thing FROM dups where dups.thing = thing_id), null) INTO my_thing;
I believe these alternatives are presented in order of readability.
Upvotes: 2