CODEWITHSUNDEEP
mysqlstored-procedures
Mark Tepper
Mark Tepper

Reputation: 43

Weird issue with a stored procedure in MySQL

I need to add a new stored procedure on our company's MySQL server. Since it's just slightly different, I used an already existing one, added the additional field and changed the name of the procedure. The weird thing now is that when I want to execute the statement, it returns:

Error Code: 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 3

reffering to the 0 in this line: SET @update_id := 0; What makes it weird is, that I queried that stored procedure by using SHOW CREATE PROCEDURE . It's saved in our database and is working fine. I just can't use it as a new stored procedure (no matter if I try to apply it to the new test database or if I use it on the existing database by giving it a new name). I searched the internet for a solution. Unfortunately to no avail. I even set up a new database with a new table and some demo values where I tried to execute the original, unaltered stored procedure. It returns the exact same error.

Here's the currently used and working stored procedure I'm talking about:

CREATE DEFINER=`root`@`localhost` PROCEDURE `customer_getcard`(IN Iinstance INT, IN Itimebuy DOUBLE, IN Iprice DECIMAL(10,2), IN Itariff INT, IN Icomment VARCHAR(128))
BEGIN
  SET @update_id := 0;
  UPDATE customer_shop SET state = 1, id = (SELECT @update_id := id), instance=Iinstance, timebuy=Itimebuy, price=Iprice, comment=Icomment WHERE tariff=Itariff AND state = 0 LIMIT 1; 
  SELECT * FROM customer_shop WHERE id = @update_id;
END

I hope you guys can help me as I am completely out of ideas what's wrong. :/

Regards, Mark

Upvotes: 1

Views: 248

Answers (2)

eggyal
eggyal

Reputation: 126035

You need to define an alternative command delimiter, as MySQL currently thinks your CREATE PROCEDURE command ends at the first ; it encounters (on line 3, after the 0), which would be a syntax error as it's after a BEGIN but before the corresponding END:

DELIMITER ;; -- or anything else you like

CREATE PROCEDURE
  ...
END;;        -- use the new delimiter you chose above here

DELIMITER ;  -- reset to normal

Upvotes: 1

Johann Tagle
Johann Tagle

Reputation: 106

MySQL stored procedures do not use ":=" for value assignment, just use "=".

Also don't think "id = (SELECT @update_id := id)" is acceptable. Here's an alternative solution (untested):

CREATE DEFINER=`root`@`localhost` PROCEDURE `customer_getcard`(IN Iinstance INT, IN Itimebuy DOUBLE, IN Iprice DECIMAL(10,2), IN Itariff INT, IN Icomment VARCHAR(128))
BEGIN
  select id into @update_id from customer_shop WHERE tariff=Itariff AND state = 0 LIMIT 1;
  UPDATE customer_shop SET state = 1, instance=Iinstance, timebuy=Itimebuy, price=Iprice, comment=Icomment where id = @update_id;
  SELECT * FROM customer_shop WHERE id = @update_id;
END

You may also want to put error handlers in case there's no matching row to be edited.

Upvotes: 0

Related Questions