MySQL generates many new result-set

Question

I've got MySQL procedure which I execute in MySQL Workbench. The problem is that it generates many new result-set, and GUI client show it (...many times) -

I found solution, which I can use instead just select clause to avoid generating result-sets. It looks like:

SELECT EXISTS(
  SELECT ...
)
INTO @resultNm

Unfortunately it won't work with LIMIT ?,1 where ? is my variable 'i'. I also cant use just 'i' instead % because I am working on MySQL 5.1 (and limit clauses can't be done in other way). So my question - are other possibilities to hide result-sets?

CREATE PROCEDURE LOOPDOSSIERS(starter integer, finish integer)
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE row_oid binary(16);
DECLARE row_str VARCHAR(256);
DECLARE row_old VARCHAR(256);

SET i=starter;
WHILE i<finish DO 
-- SET row_str = ();
SET @row_str = 'select CAST(concat(d.prefixe_numero,d.numero) as CHAR) from courrier_concerne_dossier as x
join dossier as d on 
 d.oid = x.dossier_oid limit ?,1';
PREPARE stmt1 FROM @row_str;
EXECUTE stmt1 USING @i;

SET @row_oid = 'select x.courrier_oid
from courrier_concerne_dossier as x
join dossier as d on  
 d.oid = x.dossier_oid LIMIT ?,1';
PREPARE stmt2 FROM @row_oid;
EXECUTE stmt2 USING @i;


select dossiers_str from courrier_envoye where oid = row_oid into row_old;
update courrier_envoye set dossiers_str = CAST(CONCAT(row_str, " ", row_old) AS CHAR) where oid = row_oid;

SET i = i + 1;
END WHILE;
End;
;;

possible duplicate of [MySQL Stored Procedure returning multiple record sets](http://stackoverflow.com/questions/20629558/mysql-stored-procedure-returning-multiple-record-sets) — Hans, Mar 11 '14 at 14:17
But if I put DO before SELECT what will be stored into @variable? — boski, Mar 11 '14 at 14:36

score 0 · Answer 1 · edited May 23 '17 at 12:05

LIMIT without an ORDER BY clause doesn't have a well defined behavior. For your parameters to work in a sensible way, you'll need to order by something. The starter and finish variables aren't very meaningful at the moment, but it's currently not clear what they're intended to be.

I think you can probably accomplish this whole procedure in a single query using the syntax in this answer (also probably much faster). That probably won't work with LIMIT, but I'd highly recommend using a range of some kind in the where clause rather than a limit anyway.

MySQL generates many new result-set

1 Answers1