在MySQL存储过程中将表作为参数发送表时出错 [英] ERROR when send table as parameter in MySQL store procedure

问题描述

我写了一个存储过程.它可以正常执行，但是当我调用该过程时，它显示错误:

I wrote a store procedure. It executes properly, but when I call the procedure, it shows the error:

错误代码:1327未声明的变量: 第三

Error Code : 1327 Undeclared variable: Third

请在下面查看我的程序:

Please see my procedure below:

DELIMITER $$
USE `db_test`$$
DROP PROCEDURE IF EXISTS `test_proc`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `test_proc`(IN newsInfoTable VARCHAR(100))
BEGIN     
    SET @sql_stam = CONCAT('SELECT news INTO ', @news,' FROM ',newsInfoTable,' WHERE ',CURDATE(),'=?;');      
    PREPARE s1 FROM @sql_stam;     
    SET @where_param = DATE_FORMAT(date_time,'%Y-%m-%d');     
    EXECUTE s1 USING @where_param;    
    SELECT @news;          
 END$$
DELIMITER ;

调用参数:

USE db_test;
CALL test_proc('tbl_morning_news');

推荐答案

将代码更改为:

DELIMITER $$
USE `db_test`$$
DROP PROCEDURE IF EXISTS `test_proc`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `test_proc`(IN newsInfoTable VARCHAR(100))
BEGIN     
    SET @sql_stam = CONCAT( 'SELECT news INTO @news FROM ',newsInfoTable
                           ,' WHERE DATE(`date_time`) = CURDATE()' );      
    PREPARE s1 FROM @sql_stam;         
    SELECT @news;          
 END$$
DELIMITER ;

这篇关于在MySQL存储过程中将表作为参数发送表时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！