无法在mysql中存储execute语句的返回值 [英] unable to store return value from execute statement in mysql

问题描述

我使用MySQL，试图创建一个存储的proc来检索任何给定表中varchar列的最大值.我想将列的值增加1，然后用它来存储其他字段.我不希望该列为int并自动递增.

我现在拥有的存储过程是 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 ;

编译时没有看到错误，但是在执行过程时发生以下错误.

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

请帮助.

解决方案

您可以在过程中替换

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

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;

这篇关于无法在mysql中存储execute语句的返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！