Syntax issues with MySQL procedure

Question

I have this procedure created from I could find on stack overflow. I want to get rows from all tables that have a certain column in them, for a specific value. So:

• get all tables with the column

• get all rows from the tables that have column value of x

  @DELIMITER $$
  CREATE PROCEDURE db.selectStuff ()
  BEGIN
    DECLARE a varchar(100);
    DECLARE i CURSOR FOR SELECT DISTINCT TABLE_NAME from INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME="my_column_name";
    OPEN i;
    read_loop: LOOP
      FETCH i INTO crt_table;
      SELECT * FROM crt_table WHERE my_column_name=some_id;
    END LOOP read_loop;
    CLOSE i;
  END$$
  @DELIMITER;
  call selectStuff();
  

I am getting this

[Code: 1064, SQL State: 42000] 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 'DECLARE i CURSOR FOR SELECT DISTINCT TABLE_NAME from INFORMATION_SCHEMA.COLUMNS ' at line 1 when running this from DBVisualizer Pro 9.5.7

The select works.

UPDATE: FROM the comments bellow I changed the code to:

@DELIMITER $$
CREATE PROCEDURE selectStuff ()
BEGIN
  DECLARE s VARCHAR(300);
  DECLARE i CURSOR FOR SELECT CONCAT('SELECT * FROM ',c.TABLE_NAME,' where `my_column_name`=1') as s from INFORMATION_SCHEMA.COLUMNS c WHERE c.COLUMN_NAME="my_column_name";
  OPEN i;
  do_stuff: LOOP
    FETCH i INTO s;
    PREPARE run FROM s;
    EXECUTE run;
    DEALLOCATE PREPARE run;
  END LOOP do_stuff;
  CLOSE i;
END$$
@DELIMITER;

call selectStuff();

I am getting the same error.

NOTE: Running

SELECT CONCAT('SELECT * FROM ',c.TABLE_NAME,' where `my_column_name`=1') as s from INFORMATION_SCHEMA.COLUMNS c WHERE c.COLUMN_NAME="my_column_name" 

Does actually provide the correct SQL statements that would get me what I want.

UPDATE: Running it in dbForge Studio Express:

DELIMITER $
CREATE PROCEDURE selectStuff()
BEGIN
  DECLARE done TINYINT DEFAULT 0;
  DECLARE s VARCHAR (3000);
  DECLARE i CURSOR FOR SELECT CONCAT("SELECT * FROM ", c.TABLE_NAME, " where my_column_name=1") as QUERY from INFORMATION_SCHEMA.COLUMNS c WHERE c.COLUMN_NAME="my_column_name";
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  OPEN i;
  do_stuff: LOOP
    FETCH i INTO s;
    IF done = 1 THEN LEAVE do_stuff;
    END IF;
    PREPARE stmt FROM s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP do_stuff;
  CLOSE i;
END$
DELIMITER;

#call selectStuff();

I get some other error, but the cursor part passes.

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 's;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP do_stuff;

Answer 1

OK, so answering my own question.

Thank you @Solarflare for the very useful links.

By following this answer here, I was able to get to the last Update in the question above. Seems that the errors were caused by the fact that the PREPARE statement cannot refer to a local variable, as explained in the answer here

A statement prepared in stored program context cannot refer to stored procedure or function parameters or local variables because they go out of scope when the program ends and would be unavailable were the statement to be executed later outside the program. As a workaround, refer instead to user-defined variables, which also have session scope;

THUS, the final code is this:

DELIMITER $
DROP PROCEDURE IF EXISTS myName;
CREATE PROCEDURE myName(IN id INT)
BEGIN
  DECLARE s VARCHAR(255);
  DECLARE cont TINYINT DEFAULT 1;
  DECLARE crs CURSOR FOR SELECT CONCAT("SELECT * FROM `", c.TABLE_NAME, "` where `my_column`=",id) as Q from INFORMATION_SCHEMA.COLUMNS c WHERE c.COLUMN_NAME="my_column";
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET cont=0;
  SET @sys_var = ''; 
  #THIS is where we declare a user defined variable to 
  #which we pass the actual string we want to execute as sql
  OPEN crs;
  WHILE cont=1 DO
    FETCH crs INTO s;
    SET @sys_var =s; 
    #SELECT @sys_var as ''; #this is just to test if the strings are correct
    PREPARE stmt FROM @sys_var;   
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END WHILE;
  CLOSE crs;
END$
DELIMITER;

Notice how I am still fetching the cursor into the varchar, but then I am also setting the variable sys_var to have the same value, before passing it to PREPARE I have also switched to a WHILE DO because it's easier for me to use, no other reason.

Note: I have also added a parameter, but it isn't relevant for the question.