I have the following stored procedure. The idea is to get a list of databases and execute an sql statement.
DELIMITER $$
CREATE PROCEDURE updateMySQL (
IN theSQL varchar(4000)
)
BEGIN
DECLARE finished INTEGER DEFAULT 0;
DECLARE theDatabases varchar(100) DEFAULT "";
-- declare cursor for employee email
DEClARE curDatabase
CURSOR FOR
SELECT schema_name FROM information_schema.schemata where SCHEMA_NAME = 'mydb' order by 1;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET finished = 1;
OPEN curDatabase;
getDatabase: LOOP
FETCH curDatabase INTO theDatabases;
IF finished = 1 THEN
LEAVE getDatabase;
END IF;
-- build email list
-- SET emailList = CONCAT(theDatabases,";",emailList);
SET @sql:=CONCAT('USE ',@curDatabase);
PREPARE dynamic_statement FROM @SQL;
EXECUTE dynamic_statement;
PREPARE dynamic_statement FROM @theSQL;
EXECUTE dynamic_statement;
END LOOP getDatabase;
CLOSE curDatabase;
END$$
DELIMITER ;
I am attempting to execute the stored procedure like this,
SET @theSQL = 'ALTER VIEW `Reports` AS
SELECT DISTINCT
`tableA`.`Id` AS `Id`,
`tableA`.`letterId` AS `letterId`
FROM
`mytable` `tableA`
ORDER BY 1';
call updateMySQL(@theSQL);
EDIT There was an error on executing the procedure, 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 'NULL' at line 1
I am using mysql 8.0.17
Investigate carefully MySQL 8.0 Reference Manual / SQL Statements / Prepared Statements, the section "SQL Syntax Allowed in Prepared Statements".
This section claims FULL list of statements which are allowed in Prepared statements. ALTER VIEW is NOT listed. So it is NOT allowed.
Use DROP VIEW and CREATE VIEW instead.
Always receive and investigate all error messages.
You should change this part
SET @sql:=CONCAT('USE ',@curDatabase);
PREPARE dynamic_statement FROM @SQL;
EXECUTE dynamic_statement;
PREPARE dynamic_statement FROM @theSQL;
EXECUTE dynamic_statement;
to this:
SET @sql:=CONCAT('USE ',@curDatabase);
PREPARE dynamic_statement FROM @SQL;
EXECUTE dynamic_statement;
DEALLOCATE PREPARE dynamic_statement; /* don't forget to deallocate */
/* there's a difference between the variables @theSQL and theSQL (your parameter) */
/* IIRC prepare statements need user defined variables or a syntax error occurs. Therefore I simply assign the parameter to a user-defined variable */
SET @theSQL = theSQL;
PREPARE dynamic_statement FROM @theSQL;
EXECUTE dynamic_statement;
DEALLOCATE PREPARE dynamic_statement;
Read more about user-defined variables here: https://dev.mysql.com/doc/refman/8.0/en/user-variables.html
Here the differences are explained: https://stackoverflow.com/a/1010042/447489
When you don't initialize them, their content is just NULL. Since there's a difference between user-defined variables and local variables (and also your parameter variable), your current solution did nothing.