MySQL stored procedure syntax

Thomas Teilmann

I am trying to create a very basic stored procedure in MySQL. It has to increment an int value in a table. I also want the stored procedure to be dropped if already exists. The sql inside the stored procedure works fine on its own btw.

I get this error:

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 'END' at line 5

i have tried:

DELIMITER $$
DROP PROCEDURE IF EXISTS sp_version_increment$$
CREATE PROCEDURE `sp_version_increment`()
BEGIN
    SET @ver = (SELECT version FROM version LIMIT 1) + 1;
    UPDATE version SET version = @ver
END$$
DELIMITER


DELIMITER $$
DROP PROCEDURE IF EXISTS sp_version_increment$$
CREATE PROCEDURE `sp_version_increment`()
BEGIN
    SET @ver = (SELECT version FROM version LIMIT 1) + 1;
    UPDATE version SET version = @ver
END

SOLUTION:

DELIMITER $$
DROP PROCEDURE IF EXISTS sp_version_increment$$
CREATE PROCEDURE `sp_version_increment`()
BEGIN
    SET @num = (SELECT number FROM version LIMIT 1) + 1;
    UPDATE version SET number = @num;
END

Thomas Teilmann

I found an answer myself:

DELIMITER $$
DROP PROCEDURE IF EXISTS sp_version_increment$$
CREATE PROCEDURE `sp_version_increment`()
BEGIN
    SET @num = (SELECT number FROM version LIMIT 1) + 1;
    UPDATE version SET number = @num;
END

I still have no idea why it wouldnt accept the other queries i made, so if anyone can spare a few words on the subject, i'd be glad :)

Collected from the Internet

Please contact [email protected] to delete if infringement.