mysql select 到变量_在mysql中使用select到局部变量和准备好的语句中

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 ;

解决方案

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