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

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.


Need Your Help

Run underscore code on click

underscore.js

I have the following the underscore code which displays products, I was wondering if there were a way to run this code when the user clicks an anchor link, in this case a.filter-btn the reason bein...

How do you save a plot in Octave 3.0.5 if it has latex in it?

latex png printing save octave

My school has Matlab but I can't use it at home so I am trying to learn Octave. I am having trouble saving plots as png files so I can put them in a report.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.