MySQL procedure not working (correction)

Question

I need to get all names of tables that match with the regex and for that value given do an update query, to do this I created the following MySQL procedure but MySQL tells me that I have an error at line 3

I have the following MySql procedure:

CREATE PROCEDURE example()
BEGIN
    DECLARE bdone INT;
    DECLARE var1 VARCHAR(250);
    DECLARE curs CURSOR FOR  SHOW tables like '%contenidos_extra%';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
    OPEN curs;
    SET bdone = 0;
    REPEAT
        FETCH curs INTO var1,, b;
        UPDATE var1 SET `valor`='modified' WHERE nombre = 'prueba';
    UNTIL bdone END REPEAT;
    CLOSE curs;
END

mysql tells me that error:

#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

Your procedure does not appear to have any regexes. Your `like` clause uses wildcard matching, not regular expressions. — Robert Columbia, Aug 31 '16 at 12:07
Mysql should tell you what the error is.. When asking questions its important to include the error message you've been given. It's worth editing that into your question. http://www.catb.org/esr/faqs/smart-questions.html — Philip Couling, Aug 31 '16 at 12:11
mysql tells me this: 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 — bcg, Aug 31 '16 at 12:12

score 2 · Accepted Answer · edited May 23 '17 at 10:33

You need to change the delimiter before writing the procedure:

delimiter //

CREATE PROCEDURE example()
BEGIN
    DECLARE bdone INT;
    DECLARE var1 VARCHAR(250);
    DECLARE curs CURSOR FOR  select table_name from information_schema.tables where table_name like '%contenidos_extra%';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
    OPEN curs;
    SET bdone = 0;
    REPEAT
        FETCH curs INTO var1;
        UPDATE var1 SET `valor`='modified' WHERE nombre = 'prueba';
    UNTIL bdone END REPEAT;
    CLOSE curs;
END
//

delimiter ;

Note you can't use SHOW tables in a procedure.

See here: http://dev.mysql.com/doc/refman/5.7/en/stored-programs-defining.html

I've just re-read this code and realised you have another problem. You will need to perform the updates using dynamic SQL as you can't specify a table name in a variable. You must use dynamic SQL statements for this:

How To have Dynamic SQL in MySQL Stored Procedure

MySQL procedure not working (correction)

1 Answers1