mysql - 准备 stmt 行抛出错误

Question

需要帮助来完成这个简单的任务。该 sp 应该提供一个结果集，并将从 MS-Access-Database 报告目的中调用。

表名可变，但以数字 (lsid) 结尾。使用串联。max_prepared_statement_count 默认值为 16382

有什么好的想法吗？

谢谢托马斯

CREATE DEFINER=`root2`@`%` PROCEDURE `sp_Quickview02`(IN lsid int)
BEGIN
set @tbl = concat('shape_tokens_',lsid); -- tablename

set @sql= 'select base.Status, count(base.token) as Anzahl 
                    from (select ?.completed, if(?.completed = ''N'',''offen'', ''abgeschlossen'') as Status,
            ?.token from ?) base group by base.status;'

PREPARE stmt FROM @sql; -- error is thrown here
EXECUTE stmt using @tbl;
DEALLOCATE PREPARE stmt;
END

Answer 1

如果您检查变量 @sql 值，那么它将为您提供以下输出，该输出无法执行，因此请更正它。

SELECT base.Status, COUNT(base.token) AS Anzahl 
                    FROM (SELECT ?.completed, IF(?.completed = 'N','offen', 'abgeschlossen') AS STATUS,
            ?.token FROM ?) base GROUP BY base.status;

按以下方式更改-

CREATE DEFINER=`root2`@`%` PROCEDURE `sp_Quickview02`(IN lsid int)
BEGIN
set @tbl = concat('shape_tokens_',lsid); -- tablename

SET @sql= CONCAT('select base.Status, count(base.token) as Anzahl 
                    from (select a.completed, if(a.completed = ''N'',''offen'', ''abgeschlossen'') as Status,
            a.token from ',@tbl,' as a) base group by base.status;');

PREPARE stmt FROM @sql; -- error is thrown here
EXECUTE stmt using @tbl;
DEALLOCATE PREPARE stmt;
END