SQL - 过程列错误 [英] SQL - Procedure column error

问题描述

我正在尝试创建一个执行全选命令的存储过程，因此我不必一直输入它.但是，当我调用采购时，它给了我一个列错误.

I’m trying to create a stored procedure that does a select everything command, so I don’t have to type it out all the time. However, when I call the procure it gives me a column error.

DELIMITER $$
create procedure LIST(in tableName varchar(35))
    BEGIN 
        SELECT * FROM tableName ;  
    END$$

错误

mysql> CALL LIST(Customers);
ERROR 1054 (42S22): Unknown column 'Customers' in 'field list'

推荐答案

任何动态 sql 都必须使用 MySQL 准备好的语句.

Any dynamic sql must use MySQL Prepared Statements.

DROP PROCEDURE IF EXISTS `LIST`;
DELIMITER $$
create procedure LIST(in tableName varchar(35))
BEGIN 
    SET @sql=CONCAT('select * from ',tablename);
    PREPARE stmt1 FROM @sql;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stm1;
END$$

测试:

call LIST('course');
call LIST('tutor');

这篇关于SQL - 过程列错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！