MySQL存储过程（SQL执行错误＃1064） [英] MySQL Stored Procedure (SQL execution error #1064)

问题描述

CREATE PROCEDURE get_scheduledMsg()
BEGIN
DECLARE _dateformat varchar(100);
SET _dateformat = SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');
SELECT * FROM studentdetails WHERE classCode IS IN (SELECT class FROM messagedetails WHERE isStatus ='1' AND datecreated = _dateformat) 
AND sectionCode IS IN (SELECT class FROM messagedetails WHERE isStatus ='1' AND datecreated = _dateformat);
END;


SQL execution error #1064. Response from database:

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 'SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');
SELECT * FROM studentdetails WHERE' at line 4

推荐答案

首先，您似乎使用系统变量而不是本地变量变量......应该是 @dateformat



另外，根据MySQL的版本，你不需要需要声明局部变量 - MySQL将通过 SET 声明它们



你也不需要那个 SELECT in

Firstly you appear to be using a system variable instead of a local variable ... that should be @dateformat

Also, depending on the version of MySQL you don't need to declare local variables - MySQL will declare them via the SET

You also don't need that SELECT in

SET _dateformat = SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');

尝试以下代替

Try the following instead

CREATE PROCEDURE get_scheduledMsg()
BEGIN
SET @dateformat = DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');
SELECT * FROM studentdetails WHERE classCode IS IN (SELECT class FROM messagedetails WHERE isStatus ='1' AND datecreated = @dateformat)
AND sectionCode IS IN (SELECT class FROM messagedetails WHERE isStatus ='1' AND datecreated = @dateformat);
END;

如果您仍有问题，请尝试以下代替

If you are still having issues then try the following instead

DELIMITER

CREATE PROCEDURE get_scheduledMsg（）
BEGIN
SET @ dateformat = DATE_FORMAT（NOW（），' ％Y-％m-％d％H：％i'< /跨度>）;
SELECT * FROM studentdetails WHERE classCode IS IN （ SELECT class < span class =code-keyword> FROM messagedetails WHERE isStatus = ' 1' AND datecreated = @ dateformat ）
AND sectionCode IS IN （ SELECT 类 FROM messagedetails WHERE isStatus = ' 1' AND datecreated = @ DATEFORMAT ）;
END

CREATE PROCEDURE get_scheduledMsg() BEGIN SET @dateformat = DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i'); SELECT * FROM studentdetails WHERE classCode IS IN (SELECT class FROM messagedetails WHERE isStatus ='1' AND datecreated = @dateformat) AND sectionCode IS IN (SELECT class FROM messagedetails WHERE isStatus ='1' AND datecreated = @dateformat); END

DELIMITER;

DELIMITER ;

我现在已经注意到你正在比较 datecreated with @dateformat 这意味着该列 varchar 表 messagedetails - 将日期/日期时间值存储在varchar列中是一个非常糟糕的主意 - 使用正确的数据类型

I've now noticed that you are comparing datecreated with @dateformat which implies that that column is a varchar on table messagedetails - it is a very bad idea to store date/datetime values in varchar columns - use the proper datatype

这篇关于MySQL存储过程（SQL执行错误＃1064）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！