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

Question

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.

wondering if using the `selec ... into variable` syntax would prevent it being treated as a select with a resultset? i.e. `SELECT stringField INTO dummyString FROM ... `. — Ryan Vincent, Sep 03 '16 at 09:04
Wow, that did it, and it significantly cut down on the amount of time that it takes the query to run (I guess because it's not wasting time drawing the tables on the screen). If you want to write that out as an answer, I'll mark it at the correct answer. — , Sep 06 '16 at 13:39

Ryan Vincent · Accepted Answer · 2016-09-06T17:23:24.560

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.

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

1 Answers1