如下存储过程,我先拼一个字符串,比如 'SELECT COUNT(*) FROM tableName',然后执行这个字符串,OK,没问题。但当这个字符串中包含变量时就执行不了了,比如'SELECT COUNT(*) FROM _table; --_table为变量',请高手帮忙,不甚感激:DROP PROCEDURE IF EXISTS `sp_w_select`;
CREATE PROCEDURE `sp_w_select`(
OUT rowsTotal INT,
IN tableName VARCHAR(255)
)
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE tempCount VARCHAR(1000);
DECLARE tempSelect VARCHAR(1000); --问题出在下面这句,当执行的字符串中包含变量时怎么也不行
SET @tempCount = CONCAT('SET rowsTotal = (SELECT COUNT(*) FROM ',tableName,')'); PREPARE countStmt FROM @tempCount;
EXECUTE countStmt;
DEALLOCATE PREPARE countStmt;
SET @tempSelect = CONCAT('SELECT * FROM ', tableName);
PREPARE mainStmt FROM @tempSelect;
EXECUTE mainStmt;
DEALLOCATE PREPARE mainStmt;
END;
CREATE PROCEDURE `sp_w_select`(
OUT rowsTotal INT,
IN tableName VARCHAR(255)
)
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE tempCount VARCHAR(1000);
DECLARE tempSelect VARCHAR(1000); --问题出在下面这句,当执行的字符串中包含变量时怎么也不行
SET @tempCount = CONCAT('SET rowsTotal = (SELECT COUNT(*) FROM ',tableName,')'); PREPARE countStmt FROM @tempCount;
EXECUTE countStmt;
DEALLOCATE PREPARE countStmt;
SET @tempSelect = CONCAT('SELECT * FROM ', tableName);
PREPARE mainStmt FROM @tempSelect;
EXECUTE mainStmt;
DEALLOCATE PREPARE mainStmt;
END;
.
.
.
SET @tempCount = CONCAT('SELECT COUNT(*) INTO @num FROM ',tableName);
set rowsTotal=@num;