Execute sql using a stored procedure in mysql

Question

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

I don't quite get this SELECT schema_name FROM information_schema.schemata where SCHEMA_NAME = 'mydb' order by 1; - will only return 1 row — P.Salmon, Aug 05 '20 at 09:25
@P.Salmon SCHEMA_NAME = 'mydb' will be specific to your database. So you can put your database name in place of 'mydb' and try again — Bisoux, Aug 05 '20 at 09:25
This alters nothing - query returns ONE row always, because database names must be (and respectively they are) unique. I.e. you simply execute your query on specific database which is hardcoded in SP. — Akina, Aug 05 '20 at 09:43
It returns one row but that should not stop it from executing the sql. I can always put " like 'mydb%' ". It should still be able to execute the sql passed as parameter. — Bisoux, Aug 05 '20 at 09:45

score 2 · Answer 1 · answered Aug 05 '20 at 09:49

2

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.

answered Aug 05 '20 at 09:49

Akina

39,301
5
14
25

I changed my stored procedure to drop the view but I am getting an error. I hadn't noticed the error previously but it is the same error when the procedure had ' ALTER VIEW'. I have edited my question with the error message – Bisoux Aug 05 '20 at 10:14
@Bisoux Now pay attention - error message refers to `'NULL'` whereas your SQL text (either SP and query) does not contain this substring. – Akina Aug 05 '20 at 10:20
2

@Bisoux I have covered this in my answer. Also note, that multiple statements in a prepared statement are not allowed. Instead of dropping and creating the view, you can do `CREATE OR REPLACE VIEW view_name AS SELECT ...` – fancyPants Aug 05 '20 at 10:49

fancyPants · Accepted Answer · 2020-08-05T09:30:27.900

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.

Execute sql using a stored procedure in mysql

2 Answers2