MySQL server stored procedure syntax error

Question

I am having some trouble with creating a Stored Procedure for a MySQL database.

Here is a Select statement that works:

use canningi_db_person_cdtest;
SELECT *
FROM pet
WHERE name = 'Puffball';

Here is my Stored Procedure that does not work:

use canningi_db_person_cdtest;
CREATE PROCEDURE GetAllPets() 
BEGIN 
SELECT *
FROM pet
WHERE name = 'Puffball'; 
END

I am getting the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5

How can I get this working?

EDIT

How do I call this Stored Procedure? I have tried this with no result:

use canningi_db_person_cdtest;
CALL GetAllPets();

This is the error:

#1312 - PROCEDURE canningi_db_person_cdtest.GetAllPets can't return a result set in the given context

score 1 · Accepted Answer · answered Sep 28 '13 at 01:45

1

Add a delimiter to end your procedure. Example:

use canningi_db_person_cdtest;

DELIMITER //

CREATE PROCEDURE GetAllPets() 
BEGIN 
    SELECT *
    FROM pet
    WHERE name = 'Puffball'; 
END//

If you need more information, please refer to http://dev.mysql.com/doc/refman/5.1/en/stored-programs-defining.html

answered Sep 28 '13 at 01:45

rcs

6,713
12
53
75

Thanks rcs. Can you have a look at my edit on calling the Stored Procedure? – Simon Sep 28 '13 at 02:17
The `CALL GetAllPets();` should work fine. How is it possible to have error : `EXEC GetAllPets()`? Did you type EXEC instead of CALL? – rcs Sep 28 '13 at 02:26
Sorry, I copied the wrong error message. I have updated the post. – Simon Sep 28 '13 at 02:31
Are you executing the `CALL` statement in the mysql directly (e.g. through mysql workbench), or are you calling it from your application? Some users reported issue with PHP as in http://stackoverflow.com/questions/1200193/cant-return-a-result-set-in-the-given-context. – rcs Sep 28 '13 at 02:36
I am executing the CALL statement from phpMyAdmin that is part of cPanel. Will this not work? – Simon Sep 28 '13 at 02:43
There has been issues with phpMyAdmin as well. Refer to http://stackoverflow.com/questions/2360371/mysql-stored-procedures-not-working-with-select-basic-question and http://stackoverflow.com/questions/2842263/why-am-i-getting-mysql-error-1312-when-using-a-simple-stored-procedure. Sometimes the error is not about the syntax, but about the software that is being used. – rcs Sep 28 '13 at 02:50

score 0 · Answer 2 · answered Sep 28 '13 at 02:25

In this particular case, since you have only one statement in your procedure, you don't need to change DELIMITER nor use BEGIN...END block.

You procedure might look like this

CREATE PROCEDURE GetAllPets() 
  SELECT *
    FROM pet
   WHERE name = 'Puffball';

And use it like this

CALL GetAllPets();

Here is SQLFiddle demo

MySQL server stored procedure syntax error

2 Answers2

Linked

Related