MySQL script with procedure (error 1064)

Question

mysql> source queries.txt

ERROR 1064 (42000): 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 'r_id INT) BEGIN SELECT temp.id,SUM(temp.hours*(e.salary/140)) AS cost FROM em' at line 1

So i am getting the above error when I compile the following script..
queries.txt

DELIMITER $$
CREATE PROCEDURE pro1(ΙΝ r_id INT)
BEGIN
    SELECT temp.id,SUM(temp.hours*(e.salary/140)) AS cost 
    FROM employee e INNER JOIN (SELECT r.id,w.hours, w.afm FROM repair r INNER JOIN works w ON r.id=w.id WHERE r.id=r_id) AS temp 
    ON e.afm=temp.afm GROUP BY temp.id;
END$$
DELIMITER ;

The code between begin and end is running just fine if I replace r_id with any int, as shown below:
enter image description here
As you can see the only difference is at the WHERE statement, where I have placed 20013 instead
of the procedure's parameter r_id. The rest is just a copy paste. Any tips are appreciated!

Have you tried this query directly on your database engine? May be you have already created this procedure, so that still exists. — Ataboy Josef
– Ataboy Josef, Commented Dec 13, 2014 at 7:50
@usermesam0023 i ran the command: "show procedure status" in my database and it returned an empty set... But since its giving me the error it couldn't have saved the procedure. — loukwn
– loukwn, Commented Dec 13, 2014 at 7:56

Ataboy Josef · Accepted Answer · 2014-12-13 08:17:08Z

1

Anyway try this:

   DELIMITER $$
   DROP PROCEDURE IF EXISTS pro1;

    CREATE PROCEDURE pro1(ΙΝ r_id INT)
    BEGIN
        SELECT temp.id, SUM(temp.hours*(e.salary/140)) AS cost 
        FROM employee e
        INNER JOIN
            (SELECT r.id, w.hours, w.afm
             FROM repair r
             INNER JOIN
              works w
             ON r.id = w.id WHERE r.id = r_id) AS temp 
        ON e.afm = temp.afm GROUP BY temp.id;

    END$$
    DELIMITER ;

Run this query in your DB query engine and make sure that there is no trouble with the code. And then use external files to store it.

answered Dec 13, 2014 at 8:17

Ataboy Josef

2,1093 gold badges24 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

loukwn Over a year ago

Still the same, although it's quite interesting that the error stopped at "SUM(temp.hour*(e.salary/140)) AS", instead of "FROM em" as it did earlier.. I don't know whether this has something to do with it.

Rahul · Accepted Answer · 2014-12-13 08:48:38Z

1

It looks like a very minute one. I actually don't see any issue with your procedure body except the below. Change your end delimiter statement

from

END$$

to

END $$

answered Dec 13, 2014 at 8:48

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

1 Comment

loukwn Over a year ago

nah.. doesn't make any difference.. I'm starting to think whether it has something to do with my setup of mysql or something.. However I never had any issue before.

loukwn · Accepted Answer · 2014-12-13 09:59:04Z

0

It seems that the code was right all along.. It's just that the "IN" (in the arguments was was written in greek letters).. So now it is fixed! Thanks everyone for your time, and upvoted for giving it a try!

answered Dec 13, 2014 at 9:59

loukwn

1742 silver badges14 bronze badges

Collectives™ on Stack Overflow

MySQL script with procedure (error 1064)

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related