unable to store return value from execute statemen

Question

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.

Answer 1

You can replace in your procedure

SET @cmdString = concat("Select max(", columnName, ") into @newID from ", tableName);
PREPARE stmt FROM @cmdString;
EXECUTE stmt;
SELECT @newID;