mysql存储过程语法错误1064_MySQL存储过程（SQL执行错误＃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

解决方案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 insteadCREATE 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 insteadDELIMITER

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 ;

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