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 ;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments