3

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 ;
Improve this question

2 Answers 2

Reset to default
4

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

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

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?
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.
@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"".
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.
@Joelerr: I hope the following SQL Fiddle may help you understand what you need.
3

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

Improve this answer

5 Comments

oddly, this produces the correct results on my server, but returns only a null result on SQL Fiddle.
null is returned (indlicating the query failed)when the db user does not have the 'process' privilege, however, wchiquito's answer works even with minimal privileges.
meh & pffff. This works fabulously for vars passed in as parameters. Not so much for locally defined variables from a cursor. Had to do messy concats. Upvote on the answer. Thumbs down on stored procededures.
Upvoted as useful example of valid syntax. However some questions: The fiddle doesn't seem to do anything except compile the procedure? Doesn't show how its called, nor run with sample data? (Asking in case I missed these details somewhere in the fiddle. I've never seen a fiddle before that didn't produce output.)
Answering my own comment: "Updated Final" version produces no output, because SQL Fiddle (still) can't handle "call" of stored proc in the query panel, for MySQL. So the call was removed. Maybe keep it there, but commented out with an explanation?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.