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

Question

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 ;

Solution 1

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

Solution 2

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

Comments

[AndrewD]

Thank you! that works. I'll accept the answer, but will be using my answer in code since its more readable. Any reason why mine works without all the SET@ statements you used? Is my answer less valid or have any downsides?

[AndrewD]

I limited the question to the 'how', and excluded the why. Whether I need one or not in my specific application would be the topic of another question. This question is how to do it since there were no good examples I found. Would be great if you could answer my question about why my answer works without the SET statements.

[wchiquito]

@Joelerr: You should understand some concepts to answer your own question: see 9.4. User-Defined Variables, 13.6.4.1. Local Variable DECLARE Syntax, "Routine parameters can not be referenced in prepared statements Within the routine, see Section E.1,"Restrictions on Stored Programs"".

[AndrewD]

you might not appreciate how my eyes glaze over at a bunch of links that I've read already, hence is why I asked the question. I see nothing there that gives me examples of my specific question or an explanation of why both your answers and mine works. maybe the answer is there but its not jumping out at me.

[wchiquito]

@Joelerr: I hope the following SQL Fiddle may help you understand what you need.