How to simulate a print in a MySQL Stored Procedure

Question

I have a MySQL stored procedure with a few cursors. I want to print a value to send output back to the client. SQLyog Enterprise.

I tried declaring a variable as TEXT and concatenating inside the loop but that does not work, at least not the way I was trying to do it.

DECLARE _output TEXT;
DECLARE _ID INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT ID FROM CodeID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur1;

REPEAT
  FETCH cur1 INTO _ID;
  IF NOT done THEN
    SET _output = _ID; /*SEE ALT BELOW*/

  END IF;
UNTIL done END REPEAT;
CLOSE cur1;

SELECT _output;

I've tried:

SET _output = _output + _ID

and

SET _output = CONCAT(_output,_ID)

but they both just return NULL

SET _output = _ID; just gives me the last fetched row. Which is helpful but not entirely what I wanted.

What's the best way to have each fetched row output to screen to reproduce the MySQL print in a MySQL Stored Procedure?

Answer 1

You are doing it correctly with your SELECT _output; Anything that is selected without an INTO clause will be returned to the client.

To get all of them, you could either move the SELECT into the loop (to print each individually), or you could concat them together. The problem with your concat returning NULL was because you didn't initialize the _output to anything so it was NULL. Concatting anything with NULL will return NULL.

Try the following:

DECLARE _output TEXT DEFAULT '';
DECLARE _ID INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT ID FROM CodeID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur1;

REPEAT
  FETCH cur1 INTO _ID;
  IF NOT done THEN
    SET _output = CONCAT(",", _ID); /*SEE ALT BELOW*/

  END IF;
UNTIL done END REPEAT;
CLOSE cur1;

SELECT _output;