mysql 存储过程三个参数,如何在MySQL * Prepared *存储过程中使用多个参数

Although there are some good examples of multiple parameters being used in MySQL stored procedures, I have been unable to find a simple example that shows how to use them in a stored procedure that is prepared.

The code below returns 'Incorrect arguments to EXECUTE' when calling it using: `call test_parms('my report','example.com');

I've tried with and without '@' in front of the parameter names (just gives an unknown column error), and different variations of the code . What am I doing wrong?

DELIMITER $$

DROP PROCEDURE IF EXISTS `test_parms`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test_parms`(REPORT VARCHAR(255),DOMAIN_NAME VARCHAR(255))

BEGIN

SET @sql = "Select @DOMAIN_NAME,@REPORT";

set @REPORT=REPORT;

set @DOMAIN_NAME=DOMAIN_NAME;

PREPARE stmt FROM @sql;

EXECUTE stmt using @DOMAIN_NAME,@REPORT;

DEALLOCATE PREPARE stmt;

END$$

DELIMITER ;

解决方案

The following section of the documentation will be helpful: 13.5.1. PREPARE Syntax.

DELIMITER $$

DROP PROCEDURE IF EXISTS `test_parms`$$

CREATE PROCEDURE `test_parms`(`REPORT` VARCHAR(255), `DOMAIN_NAME` VARCHAR(255))

BEGIN

SET @`sql` := 'SELECT ? `DOMAIN_NAME`, ? `REPORT`';

SET @`REPORT` := `REPORT`;

SET @`DOMAIN_NAME` := `DOMAIN_NAME`;

PREPARE `stmt` FROM @`sql`;

EXECUTE `stmt` USING @`DOMAIN_NAME`, @`REPORT`;

DEALLOCATE PREPARE `stmt`;

END$$

DELIMITER ;

UPDATE

DELIMITER $$

DROP PROCEDURE IF EXISTS `test_parms`$$

CREATE PROCEDURE `test_parms`(`REPORT` VARCHAR(255), `DOMAIN_NAME` VARCHAR(255))

BEGIN

SELECT `DOMAIN_NAME` `DOMAIN_NAME`, `REPORT` `REPORT`;

END$$

DELIMITER ;