Andrew Andrew - 3 months ago 9
MySQL Question

MySQL command line: Don't display results of query in a stored procedure

I've got a procedure that uses a loop with a SELECT statement, but the statement is actually just to set a variable. That means there's a lot of stuff being displayed that I don't need to see, and it's flooding my terminal.

Here's an example of what I mean, though this isn't actually what I'm running (because that's company information):

DROP PROCEDURE IF EXISTS test;

DELIMITER #
CREATE PROCEDURE test()
BEGIN
SET @key:=1;
testloop: REPEAT
SELECT
@dummyString := stringField
FROM
aTable;

SET @dummyStringAll :=CONCAT(@dummyStringAll,$dummyString);
SET @key := @key + 1;

UNTIL @key>10
END REPEAT testloop;
END #
DELIMITER ;


Is it possible to run SELECT (whether inside a procedure or not) and not show the results from a SELECT query? Maybe not the most important thing in the world, but it would be helpful.

Answer

Stored procedures will return a query resultset if it isn't stored in a variable.

How does it know that you are storing the result in a variable?

Not be using variables in the query but by using the SELECT value INTO <variable> syntax in the query. see: 13.2.9.1 SELECT ... INTO Syntax

From the FAQ:

1) Can MySQL 5.6 stored routines return result sets?

Stored procedures can, but stored functions cannot. If you perform an ordinary SELECT inside a stored procedure, the result set is returned directly to the client.

So, using the 'SELECT ... INTO ...' syntax will prevent the procedure returning the resultset from a query.