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

Answers


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

Need Your Help

Selecting a value into a variable

sql sql-server tsql

I'm playing around with t-sql and would like to be able to select into some variable.

Throwing an exception in a WCF service throws a CommunicationException

wcf communicationexception

Whenever I throw an exception in my service, another exception is thrown right after it: