Avoid MySQL multi-results from SP with Execute
Posted
by hhyhbpen
on Stack Overflow
See other posts from Stack Overflow
or by hhyhbpen
Published on 2010-06-12T05:57:54Z
Indexed on
2010/06/12
6:02 UTC
Read the original article
Hit count: 274
Hi, i have an SP like
BEGIN
DECLARE ...
CREATE TEMPORARY TABLE tmptbl_found (...);
PREPARE find FROM
" INSERT INTO tmptbl_found
(SELECT userid FROM
(
SELECT userid FROM Soul
WHERE
.?.?.
ORDER BY
.?.?.
) AS left_tbl
LEFT JOIN
Contact
ON userid = Contact.userid
WHERE Contact.userid IS NULL LIMIT ?)
";
DECLARE iter CURSOR FOR SELECT userid, ... FROM Soul ...;
...
l:LOOP
FETCH iter INTO u_id, ...;
...
EXECUTE find USING ...,. . .,u_id,...;
...
END LOOP;
...
END//
and it gives multi-results. Besides it's inconvenient, if i get all this multi-results (which i really don't need at all), about 5 (limit's param) for each of the hundreds of thousands of records in Soul, i'm afraid it will take all my memory (and all in vain). Also, i noticed, if i do prepare from an empty string, it still has multi-results... At least how to get rid of them in the execute statement? And i would like to have a recipe to avoid ANY output from SP, for any possible statement (i also have a lot of "update ..."s and "select ... into "s inside, if they can produce multi's). Tnx for any help...
© Stack Overflow or respective owner