每次调用此存储过程我都会收到此错误:
#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
似乎是什么问题?
这是代码:
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 ;
解决方法:
“参数标记只能在应显示数据值的地方使用,而不能用于SQL
关键字,标识符等.”
因此,我们不能做:
从“选择ID到”准备stmt?从t1′;
我们不能使用?而不是标识符(变量名).
当您使用参数名称时:
从’SELECT id INTO rid from t1’准备stmt;
在准备的字符串中,然后服务器根本不知道rid指的是
您正在准备的声明.您可以尝试使用相同的方法在SP外部进行准备
结果:
MySQL的>从“从t1选择ID到p中”准备stmt;
错误1327(42000):未声明的变量:p
所以我只用了用户变量,见下文:
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$$
标签:mysql,stored-procedures
来源: https://codeday.me/bug/20191012/1900621.html