Using select into local variable and prepared statement in mysql

Question

Everytime call this stored procedure i get this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

What seems to be the problem?

Here is the code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `singledb`.`TOUR_TRANSFER`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `TOUR_TRANSFER`()
BEGIN

BEGIN -- for rubric table
            DECLARE tblRubric_rubric_id INT;
            SET @qry = concat('SELECT count(*) into ',tblRubric_rubric_id,' FROM zerodb2.tour_template');
            PREPARE statement FROM @qry;
            EXECUTE statement;

            SELECT tblRubric_rubric_id;
END;
END$$

DELIMITER ;

What exactly are you trying to do I think I can help. Get the count of a query and just return that? — Ray
– Ray, Commented Oct 27, 2011 at 14:40
@Ray i just want to assign a value to a local variable using select into in a prepared statement. But I found out that it wont work with local variables. See bugs.mysql.com/bug.php?id=15263 — Karina
– Karina, Commented Oct 28, 2011 at 6:31
It does work with local variables. I understand what you are trying to do I think, but there is no need to select it into a local variable in a stored procedure unless you plan on doing something with that local variable in the stored procedure. Where are you calling this stored procedure from PHP or something? If want to access that "count(*)" all you have to do is call the select statement. You don't have to select it into a variable. — Ray
– Ray, Commented Oct 30, 2011 at 18:35

Ravinder Reddy · Accepted Answer · 2020-11-28 15:04:05Z

From http://bugs.mysql.com/bug.php?id=15263:

"Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth."

So, we can not do:

PREPARE stmt FROM 'SELECT id INTO ? FROM t1';

We can not use ? instead of identifier (variable name).

And when you use parameter name:

PREPARE stmt FROM 'SELECT id INTO rid FROM t1';

in the string being prepared, then server simply do not know what that rid refers to in statement you are trying to prepare. You may try to prepare it outside SP with the same result:

mysql> prepare stmt from 'select id into p from t1';
ERROR 1327 (42000): Undeclared variable: p

So I just used user variable, see below:

DROP PROCEDURE IF EXISTS `singledb`.`TOUR_TRANSFER`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `TOUR_TRANSFER`()
BEGIN
  BEGIN -- for rubric table
    -- DECLARE @tblRubric_rubric_id INT;

    SET @tblRubric_rubric_id := 0;
    SET @qry = 'SELECT count(*) into @tblRubric_rubric_id FROM zerodb2.tour_template';
    PREPARE statement FROM @qry;
    EXECUTE statement;
  END;
END$$

Ray · Accepted Answer · 2011-10-27 14:50:17Z

0

You had a lot of syntax errors I think. This is usually how I write my stored procedures and they work. Try this so far. Basically there is no need for you to select the count into that temporary variable if you're just going to select from that temporary variable in the very next statement.

DELIMITER $$

DROP PROCEDURE IF EXISTS TOUR_TRANSFER;
CREATE PROCEDURE TOUR_TRANSFER()


BEGIN # for rubric table
DECLARE tblRubric_rubric_id INT;

SET @qry := CONCAT('SELECT count(*) FROM zerodb2.tour_template');
PREPARE statement FROM @qry;
EXECUTE statement;

#SELECT tblRubric_rubric_id; # i don't think this will work

END$$

DELIMITER ;

answered Oct 27, 2011 at 14:50

Ray

6853 gold badges7 silver badges15 bronze badges

1 Comment

Karina Over a year ago

Hi! Thanks for your response! But what i want to do is to assign a value to a variable using select into in a prepared statement. I just found out that it won't really work with local variables. Here is an excerpt from bugs.mysql.com/bug.php?id=15263 : "Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth."

Collectives™ on Stack Overflow

Using select into local variable and prepared statement in mysql

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related