CODEWITHSUNDEEP
mysqlstored-proceduresparametersprepared-statement
AndrewD
AndrewD

Reputation: 5228

How to Use Multiple Parameters in a MySQL *Prepared* Stored Procedure

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 ;

Upvotes: 3

Views: 19673

Answers (2)

AndrewD
AndrewD

Reputation: 5228

Looks like I was unnecessarily setting user defined variables prior to execution, which was included in some of the stored procedure examples, but apparently doesn't work if the stored procedure is prepared.

To fix, Replace the following code:

set @REPORT=REPORT;
set @DOMAIN_NAME=DOMAIN_NAME;

PREPARE stmt FROM @sql;
EXECUTE stmt using @DOMAIN_NAME,@REPORT;

with this : 

PREPARE stmt FROM @sql;
EXECUTE stmt;

So the corrected code looks like:

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";

PREPARE stmt FROM @sql;
EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END$$

DELIMITER ;

Update

I ended up with the following which works with any user with privileges to execute procedures ('process' permission is not required like it is for the previous code) and works correctly on SQL Fiddle:

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 @DOMAIN_NAME=DOMAIN_NAME;
        SET @REPORT=REPORT;
        PREPARE stmt FROM @sql;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT ;
            END//

SQL Fiddle - Updated Final

Upvotes: 3

wchiquito
wchiquito

Reputation: 16569

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 ;

SQL Fiddle demo

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 ;

SQL Fiddle demo

Upvotes: 4

Related Questions