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: 281

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

Related posts about mysql

Related posts about stored-procedures