How to simulate the Print function in a MySQL Stored Procedure

Question

I'm creating a MySQL Stored Procedure with a few cursors. I want to make sure the Insert/Update statements it will ultimately generate are correct before making it live.

I searched and could not find a PRINT or similar method to have it just send output back to the client, in my case 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

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

So what's the best way to have each fetched row output to screen to reproduce the MSSQL Print function 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;