mysql存储过程执行动态sql的问题

0 0

mysql存储过程执行动态sql的问题5

我现在想通过mysql的存储过程执行动态SQL，传递一个表名查询，根据表名查询表中的第一个列。

根据第一列获取其列中的最大值，下面是我写的存储过程。但始终获取不到最大值的结果，请帮忙看一下是什么原因?

DELIMITER $$
CREATE PROCEDURE genBillsNoProc(IN tableName VARCHAR(30),OUT data_t VARCHAR(100))
BEGIN
DECLARE sql_1 VARCHAR(1000);
DECLARE sql_2 VARCHAR(1000);
SET sql_1 = CONCAT("SELECT COLUMN_NAME into @colName FROM information_schema.COLUMNS WHERE table_name='",tableName,"' limit 0,1");
SET sql_2 = CONCAT("SELECT MAX(?) FROM ",tableName);

SET @frist_sql=sql_1;
PREPARE stmt FROM @frist_sql;
EXECUTE stmt;

SET data_t := @colName;
SET @second_sql=sql_2;
PREPARE stmt1 FROM @second_sql;
EXECUTE stmt1 USING @colName;
SELECT sql_2 ;
END$$

##执行存储过程
CALL genBillsNoProc('t_common_system',@data_t);

运行存储过程返回结果就是一个列名:
col_xxx

问题补充：呵呵，问题解决了

正确的SQL：

DELIMITER $$
CREATE PROCEDURE genBillsNoProc(IN tableName VARCHAR(30),OUT data_t VARCHAR(100))
BEGIN
DECLARE sql_1 VARCHAR(1000);
DECLARE sql_2 VARCHAR(1000);

SET sql_1 = CONCAT("SELECT COLUMN_NAME into @colName FROM information_schema.COLUMNS WHERE table_name='",tableName,"' limit 0,1");
#执行sql_1SQL语句;
SET @frist_sql=sql_1;
PREPARE stmt FROM @frist_sql;
EXECUTE stmt;

SET sql_2 = CONCAT("select max(",@colName,") into @sql_result from ",tableName);
#执行sql_2SQL语句;
SET @second_sql=sql_2;
PREPARE stmt1 FROM @second_sql;
EXECUTE stmt1;

#设置返回结果;
SET data_t := @sql_result;
END$$

##执行存储过程
CALL genBillsNoProc('t_common_system',@data_t);
SELECT @data_t;