How to CALL a PROCEDURE in MySQL?

Question

I've started to examine Procedures in MySQL, but all my efforts don't work. Here is my Procedure Creation:

DELIMITER //
CREATE PROCEDURE test(IN a INT)
BEGIN
    SELECT * 
    FROM `table`
    WHERE `id` = a;
END

MySQL returns O.K., no errors.

 DELIMITER ;

MySQL returns O.K., no errors.

But CALL-Statement does not work:

CALL test(8);

Returns an error: #1312 - PROCEDURE dbxyz.test can't return a result set in the given context

Now, I don't know what I've made wrong: a mistake in Procedure-Cration or an error in CALL-Statement.

--> ##########################

As of 6th Feb. 2014: Today, I've tried to find out, why my Stored Procedure does not work in Query Window of phpMyAdmin. A 'SELECT *' does not work within a Stored Procedure, but a SELECT column by column does work. Here is what I have found out: Use IN and OUT as well as INTO. Example:

 DELIMITER //
 CREATE PROCEDURE test(IN a INT, OUT b VARCHAR(12), OUT c INT)
 BEGIN
   SELECT b, c
   FROM `table`
   WHERE `id` = a
   INTO b, c;
 END

Now, this stored Procedure is able to run in phpMyAdmin:

 call test(5, @result1, @result2);
 SELECT @result1, @result2

If you just need ONE result instad of two or more, you could also use SET - Statement. Here we are:

   DELIMITER //
   CREATE PROCEDURE test(IN a INT, OUT b INT)
   BEGIN
        SET b = ( SELECT b
        FROM table
        WHERE id = a );
   END

Maybe [this](http://stackoverflow.com/questions/2360371/mysql-stored-procedures-not-working-with-select-basic-question) or [this](http://stackoverflow.com/questions/1200193/cant-return-a-result-set-in-the-given-context)? — bishop, Jan 27 '14 at 21:20
Not really. I've set up this query in Query Window in phpMyAdmin. — Peter, Jan 29 '14 at 05:49
Well, your query looks correct. I suspect this is a phpMyAdmin issue which sporadically appears, per [this answer](http://stackoverflow.com/a/2437024/2908724). If your `CALL` works in the MySQL client or in the workbench, it is the phpMyAdmin bug. If not, we have more information. — bishop, Jan 29 '14 at 12:12
Today, I've tried to find out, why my Stored Procedure does not work. A 'SELECT *' does not work within a Stored Procedure, but a SELECT column by column does work. Here is what I have found out: DELIMITER // CREATE PROCEDURE test(IN a INT, OUT b VARCHAR(12), OUT c INT) BEGIN SELECT b, c FROM `table` WHERE `id` = a INTO b, c; END Now I can run my Procedure: call test(5, @result1, @result2); SELECT @result1, @result2 — Peter, Feb 06 '14 at 11:09
What you need is a stored function and not procedure. The difference between the two is that a stored function returns a result while stored procedure is a function without a return result. You can pass variables that will be filled with data, as you did, but to have it work the original way you thought - you'd create a function. — N.B., Feb 06 '14 at 12:27
@N.B.: Strictly speaking, [stored procedures do return a result set](http://dev.mysql.com/doc/refman/5.5/en/faqs-stored-procs.html#qandaitem-B-4-1-14), but that usage seems rare in the wild. — bishop, Feb 06 '14 at 15:17
@Peter: Glad you got it working. The last `SELECT` in a procedure is [available to the calling environment](http://dev.mysql.com/doc/refman/5.5/en/faqs-stored-procs.html#qandaitem-B-4-1-14), provided you've got the right client/server connection. I think doing this through phpMyAdmin was not giving you that "correct" connection. — bishop, Feb 06 '14 at 15:19

score -3 · Accepted Answer · answered Sep 18 '17 at 20:45

How to CALL a PROCEDURE in MySQL?

1 Answers1

Linked