cmd进去mysql执行语句无返回_无法在mysql中存储执行语句的返回值

Using MySQL, I am trying to create a stored proc to retrieve the maximum value of a varchar column in any given table. I would want to increment the value of the column by 1 and then use it to store the other fields. I do not want the column to be int and auto increment.

the stored proc i have for now is

DELIMITER $$

use gounicartdb$$

DROP PROCEDURE IF EXISTS sp_getUpdatedIDFromTable$$

CREATE PROCEDURE sp_getUpdatedIDFromTable(

IN tableName varchar(50),

IN columnName varchar(50),

IN incrementValue int/*,

OUT updatedID varchar(10)*/

)

BEGIN

SET @newID = "abc";

SET @cmdString = concat("Select max(", columnName, ") from ", tableName);

PREPARE stmt FROM @cmdString;

SELECT @newID = EXECUTE stmt;

END$$

DELIMITER ;

When compiling I see no errors, but when executing the procedure the following error occurs.

14:50:48 Call sp_getUpdatedIDFromTable("user", "SNo", 1) Error Code: 1054. Unknown column 'EXECUTE' in 'field list' 0.000 sec

Please help.

解决方案

You can replace in your procedure

SET @cmdString = concat("Select max(", columnName, ") into @newID from ", tableName);

PREPARE stmt FROM @cmdString;

EXECUTE stmt;

SELECT @newID;