mysql - 存储过程 - 需要使用 SELECT 语句的结果

Question

以下存储过程将返回一个结果，其列 NoOfArticles 具有正确的总金额。

DELIMITER //

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_membersarea`(IN reqdTable varchar(30), IN ScannerID INT, IN MemberID INT)
BEGIN
declare NoOfArticles INT(11); 

SET @sql_text = concat("SELECT COUNT(*) AS NoOfArticles FROM t_annualdetails WHERE ScannerID = ", ScannerID);
PREPARE stmt FROM @sql_text;
EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END //
DELIMITER ;

到目前为止还不错。这是我的问题。我希望能够在同一个存储过程中的另一个查询中使用这个“结果”，但我不知道如何实现它。我希望它能像这样工作:

SET @NoOfArticles = NoOfArticles;   

SET @sql_text = concat("UPDATE ", reqdTable, " SET Scan = ", @NoOfArticles, " WHERE MemberID = ", MemberID);
PREPARE stmt FROM @sql_text;
EXECUTE stmt;

显然，这行不通，但我希望将“结果”(NoOfArticles) 放在一个变量中(即 @NoOfArticles)，然后在 UPDATE 语句中使用该变量 (@NoOfArticles)。

我整天都在做这个，但没有成功。我会很感激一些指导。谢谢。

Answer 1

是INTO，不是AS

DELIMITER //

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_membersarea`(IN reqdTable varchar(30), IN ScannerID INT, IN MemberID INT)
BEGIN
declare NoOfArticles INT(11); 
/*----------------------------------here-v--into-instead-of-as-----------------*/
SET @sql_text = concat("SELECT COUNT(*) INTO NoOfArticles FROM t_annualdetails WHERE ScannerID = ", ScannerID);
PREPARE stmt FROM @sql_text;
EXECUTE stmt;

DEALLOCATE PREPARE stmt;

SET @NoOfArticles = NoOfArticles;    
SET @sql_text = concat("UPDATE ", reqdTable, " SET Scan = ", @NoOfArticles, " WHERE MemberID = ", MemberID);
PREPARE stmt FROM @sql_text;
EXECUTE stmt;

/*you can also use parameters, but not for tablename*/
SET @sql_text = concat("UPDATE ", reqdTable, " SET Scan = ? WHERE MemberID = ?");
PREPARE stmt FROM @sql_text;
EXECUTE stmt USING NoOfArticles, MemberID;

END //
DELIMITER ;

编辑:

DELIMITER //

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_membersarea`(IN reqdTable varchar(30), IN ScannerID INT, IN MemberID INT)
BEGIN

SET @sql_text = concat("SELECT COUNT(*) INTO @NoOfArticles FROM t_annualdetails WHERE ScannerID = ", ScannerID);
PREPARE stmt FROM @sql_text;
EXECUTE stmt;

DEALLOCATE PREPARE stmt;

/*you can also use parameters, but not for tablename*/
SET @sql_text = concat("UPDATE ", reqdTable, " SET Scan = ? WHERE MemberID = ?");
PREPARE stmt FROM @sql_text;
EXECUTE stmt USING @NoOfArticles, MemberID;

END //
DELIMITER ;