mysql stored procedure set value from table

Question

I've got a following, simple table:

mysql> select * from version;
+----+---------+
| id | version |
+----+---------+
|  1 | 1       |
+----+---------+
1 row in set (0.00 sec)

I need to create a stored procedure that will do something (or nothing) depending on the value of this table (precisely, the only row of this table).

DELIMITER $$
DROP PROCEDURE IF EXISTS upgrade_version $$
CREATE PROCEDURE upgrade_version(current_version INTEGER, script TEXT)
BEGIN
  DECLARE version INT(11);
  SET version = (SELECT `version` FROM `version` WHERE `id` = 1 LIMIT 1);
  SELECT version;
  IF version = current_version + 1 THEN
    PREPARE upgrade_stmt FROM script;
    EXECUTE upgrade_stmt;
    UPDATE `version` SET `version` = `version` + 1 WHERE `id` = 1;
  ENDIF;
END $$
DELIMITER ;

To be called within:

CALL upgrade_version(1,'ALTER TABLE ...');

It is syntactically incorrect, mysql returns:

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 'script;
    EXECUTE upgrade_stmt;
    UPDATE `version` SET `version` = `version`' at line 7

What is wrong?

Do you get this message at procedure definition or when you try to call it ? — berty
– berty, Commented Dec 18, 2012 at 11:27

Suresh Kamrushi · Accepted Answer · 2015-08-05 14:31:06Z

5

I dont understand why you are selecting that "SELECT version", If you want to select the data you have to retrieve it into a varialbe, that statement works fine when you use it directly at the terminal. And are you sure that you have prepared_stmt in your scripts..!? possible fix is :

change the varible name version to version_v
instead of this:

SET version = (SELECT version FROM version WHERE id = 1 LIMIT 1);
SELECT version;

try this:

SELECT `version` INTO Version_v FROM `version` WHERE `id` = 1 LIMIT 1);

edited Aug 5, 2015 at 14:31

Suresh Kamrushi

16.2k13 gold badges79 silver badges93 bronze badges

answered Dec 18, 2012 at 11:41

Chella

1,5624 gold badges23 silver badges43 bronze badges

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

Comments

Bill the Lizard · Accepted Answer · 2012-12-18 15:00:09Z

4

Try this:

DELIMITER $$
DROP PROCEDURE IF EXISTS upgrade_version $$
CREATE PROCEDURE upgrade_version(current_version INT, script TEXT)
BEGIN
  SELECT `version` into @ver FROM `version` WHERE `id` = 1 LIMIT 1;  
  SET @s = script;
  IF @ver = current_version + 1 THEN
    PREPARE upgrade_stmt FROM @s;
    EXECUTE upgrade_stmt;
    DEALLOCATE PREPARE upgrade_stmt;
    UPDATE `version` SET `version` = `version` + 1 WHERE `id` = 1;
  END IF;
END $$
DELIMITER ;

edited Dec 18, 2012 at 15:00

Bill the Lizard

407k213 gold badges579 silver badges892 bronze badges

answered Dec 18, 2012 at 11:55

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

Comments

Hari Lakkakula · Accepted Answer · 2019-05-30 09:40:58Z

0

Set Value To Declare variable

       DELIMITER $$
       create procedure Testing()
           begin
            declare Regione int;   
            set Regione=(select  id from users
            where id=1) ;
            select Regione ;
           end $$
         DELIMITER;

answered May 30, 2019 at 9:40

Hari Lakkakula

3172 silver badges5 bronze badges

Collectives™ on Stack Overflow

mysql stored procedure set value from table

3 Answers 3

Comments

Comments

Set Value To Declare variable

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Set Value To Declare variable

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related