Executing CONCAT statement in stored procedure does not work

Question

I have two tables, a base table containing various information about the value 'tconst' (also the primary key) and another table linked to multiple 'tconst' values ​​from 'nconst' under the name 'titleId' .

---Base table 'titlebasics'

tconst	Title Type	...
tt0000009	Movies	...
tt0000147	Movies	...
...	...	...

---Additional information table 'knownfortitles'

id	nconst	Title Order	Title ID
1	nm0000001	1	tt0050419
2	nm0000001	2	tt0053137
...	...	...	...

The "problem" is that some values ​​in knownfortitles.titleId do not exist in titlebasics.tconst. I want to create a stored procedure where I can pass as parameters the names of two tables and two corresponding columns. This process will first check to see if the value that does not exist in the first table actually exists in the second table, and if so, adds a value to the second table called is_in_ List. It will then subsequently update this column with the boolean value from each row in the second table. I want to do this in a stored procedure because I have a lot of tables with this problem and I want to be able to use this procedure instead of writing the same code over and over with different values. However, I get an error when I try to call my program and I can't seem to fix it.

As a stored procedure, this is where I get stuck.

CREATE PROCEDURE `CheckValueExistsInBaseTable`(
    IN checkedTable VARCHAR(100),
    IN referencedBaseTable VARCHAR(100),
    IN checkedCol VARCHAR(100),
    IN referencedCol VARCHAR(100)
    )
BEGIN
    DECLARE new_column_name VARCHAR(100) DEFAULT 'is_in_baseTable';
    DECLARE sql_statement1 VARCHAR(1000) DEFAULT 'SELECT NULL;';
    DECLARE sql_statement2 VARCHAR(1000) DEFAULT 'SELECT NULL;';
    
    SET @new_column_name  = CONCAT('is_in_',referencedBaseTable);
    
    -- Add new column to checked table if it doesn't exist
    SET @sql_statement1 = CONCAT('IF (SELECT CASE WHEN EXISTS(
    选择1 
    FROM ', checkedTable, ' 
    WHERE ', checkedCol, ' NOT IN (SELECT ', referencedCol, ' FROM ', referencedBaseTable, ')) 
    THEN 1 ELSE 0 END
) = 1
THEN 
    ALTER TABLE ', checkedTable, ' ADD ', @new_column_name, ' BOOL;
ELSE 
    SELECT NULL;
END IF');
    PREPARE stmt1 FROM @sql_statement1;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
    
    -- Update is_in_referencedBaseTable column in checked table
    SET @sql_statement2 = CONCAT('UPDATE ', checkedTable, ' SET ', 
        @new_column_name, ' = CASE WHEN EXISTS(SELECT * FROM ', 
        referencedBaseTable, ' WHERE ', referencedBaseTable, '.', 
        referencedCol, ' = ', checkedTable, '.', checkedCol, ') THEN 1 ELSE 0 END');
    PREPARE stmt2 FROM @sql_statement2;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;
END

无论我尝试更改什么，这都会给我带来以下错误之一。

错误代码：1064。您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行“NULL”附近使用的正确语法

或

错误代码：1064。您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在 'IF (SELECT CASE WHEN EXISTS( 选择1 来自知名作品 WHERE titleId NOT' 在第 1 行

我还创建了测试程序来检查哪些部分可能出现问题，但两者都工作得很好，这让我更加困惑。第一个仅返回我在 CONCAT 中放入的内容，以查看其中是否存在任何语法错误。

CREATE PROCEDURE `test`(
    IN checkedTable VARCHAR(100),
    IN referencedBaseTable VARCHAR(100),
    IN checkedCol VARCHAR(100),
    IN referencedCol VARCHAR(100),
    IN new_column_name VARCHAR (100)
)
BEGIN
-- Declaring the variable and assigning the value
declare myvar VARCHAR(1000);
DECLARE new_column_name VARCHAR(100) DEFAULT 'is_in_baseTable';
SET @new_column_name  = CONCAT('is_in_',referencedBaseTable);
 
SET myvar =  CONCAT('IF (SELECT CASE WHEN EXISTS(
    选择1 
    FROM ', checkedTable, ' 
    WHERE ', checkedCol, ' NOT IN (SELECT ', referencedCol, ' FROM ', referencedBaseTable, ')) 
    THEN 1 ELSE 0 END
) = 1
THEN 
    ALTER TABLE ', checkedTable, ' ADD ', @new_column_name, ' BOOL;
ELSE 
    SELECT NULL;
END IF');

-- Printing the value to the console
SELECT concat(myvar) AS Variable;
END

此过程给出下一个结果：

IF (SELECT CASE WHEN EXISTS(
    选择1 
    来自知名作品 
    WHERE titleId NOT IN (SELECT tconst FROM titlebasics)) 
    THEN 1 ELSE 0 END
 ) = 1
 THEN 
    ALTER TABLE knownfortitles ADD is_in_titlebasics BOOL;
 ELSE 
    SELECT NULL;
 END IF

这段代码是正确的，我知道这一点是因为我使用了下面的第二个过程，该过程利用了这个确切的代码块。

CREATE PROCEDURE `test2`()
BEGIN    
    IF (SELECT CASE WHEN EXISTS(
        选择1 
        来自知名作品
        WHERE titleId NOT IN (SELECT tconst FROM titlebasics)) 
        THEN 1 ELSE 0 END
    ) = 1
    THEN 
        ALTER TABLE knownfortitles ADD is_in_titlebasics BOOL;
    ELSE 
        SELECT NULL;
    END IF;
END

此过程将列 is_in_titlebasics 添加到表 knownfortitles 中，这就是我想要发生的事情，所以这很好。此时，我完全迷失了，不知道为什么我的实际存储过程不起作用，因为它基本上是最后两个过程的组合。我暂时忽略了我希望存储过程执行的第二部分，因为我遇到的错误似乎将第一个 CONCAT 语句视为问题。

我希望这个问题是非常明显的，但我只是忽略了。欢迎任何帮助，提前致谢！

Answer 1

Thanks to P. Salmon, I learned that the problem was running the IF ... THEN statement through the prepared statement. This is impossible. After some tinkering, I came up with the following program, which works exactly as I want. I hope I can help some people who have similar problems to me.

DELIMITER $$
USE `<schema>`$$
CREATE DEFINER=`Setupinfolab`@`%` PROCEDURE `CheckValueExistsInBaseTable`(
    IN checkedTable VARCHAR(100),
    IN referencedBaseTable VARCHAR(100),
    IN checkedCol VARCHAR(100),
    IN referencedCol VARCHAR(100)
    )
BEGIN
    SET @checkedTable = checkedTable;
    SET @referencedBaseTable = referencedBaseTable;
    SET @checkedCol = checkedCol;
    SET @referencedCol = referencedCol;
    SET @new_column_name  = CONCAT('is_in_', referencedBaseTable);
    
    -- Check if there are indeed values in checkedCol that are not present in referencedCol
    SET @query1 = CONCAT('
        SELECT CASE WHEN EXISTS(
            SELECT 1 
            FROM ',@checkedTable,'
            WHERE ',@checkedCol,' NOT IN (SELECT ',@referencedCol,' FROM ',@referencedBaseTable,')) 
            THEN 1 ELSE 0 END 
            INTO @proceed');
        
    -- Adding the boolean column
    SET @query2 = CONCAT('
        ALTER TABLE ',@checkedTable,' ADD ',@new_column_name,' BOOL;');
    
    -- Inserting boolean values in new column according to presence in referencedCol
    SET @query3 = CONCAT('
        UPDATE ',@checkedTable,' SET ',@new_column_name,' = (
            CASE WHEN EXISTS(
                SELECT * 
                FROM ',@referencedBaseTable,' 
                WHERE ',@referencedBaseTable,'.',@referencedCol,' = ',@checkedTable,'.',@checkedCol,')
            THEN true ELSE false END
            ) WHERE id<>0;');
    
    PREPARE stmt1 FROM @query1;
    EXECUTE stmt1;
    
    IF @proceed = 1
    THEN 
        PREPARE stmt2 FROM @query2;
        EXECUTE stmt2;
        PREPARE stmt3 FROM @query3;
        EXECUTE stmt3;
        SELECT CONCAT(@new_column_name,' column added to table ', @checkedTable,'.') 
            AS 'Message: Done';
        DEALLOCATE PREPARE stmt2;
        DEALLOCATE PREPARE stmt3;
    ELSE 
        SELECT 'All values are present in base table. Adding column is thus unnecessary.' 
            AS 'Message: UNNECESSARY';
    END IF;
    
    DEALLOCATE PREPARE stmt1;
END$$
DELIMITER ;
;

However, I would like to point out that I haven't added a TRY ... CATCH or anything to stop the program from throwing an error. This is just the bare minimum to make it work.

Calling process

CALL CheckValueExistsInBaseTable(
    'knownfortitles',
    'titlebasics',
    'titleId',
    'tconst'
    );

Return message confirmation:

or message warning:

Only in the first case, the TINYINT(1) column will be added as needed.