OK - So I am writing my first stored procedure in mySQL (Yes - stored procedure and function support is available in mySQL 5 - look under stored routines :-)) and I kept getting this weird syntax error - Error Code : 1064 You have have an error in your SQL syntax;.
It was one of those frustrating problems - I tried everything I knew and I got nowhere. I knew that the error was wrong because I could execute the SQL statements outside the store procedure but I had no idea what the real problem was…
Finally, a good Samaritan in the form of a friend (John) of my friend Steven told me about the delimiter command. In normal cases the mySQL client interprets the ; character as the end of a SQL statement. But in the case when you are creating a stored procedure there are 2 sets of SQL for the client to deal with - one being the SQL to define the procedure itself CREATE PROCEDURE name (parameter1,..)BEGIN .. END; and the next level is the SQL in the stored procedure. If there are multiple SQL statements in the stored procedure then the client considers the ; at the end to be the close of the SQL for defining the stored procedure itself and tries to execute it and trips up… The solution is to define a different delimiter for the SQL in the stored procedure definition using the delimiter command before beginning the stored procedure definition. You must remember to revert the delimiter back to ; at the end - eg :-
delimiter // drop procedure if existsdatabasestored procedure` // create procedure `databasestored procedure(parameter1 datatype, parameter2 datatype) begin declare localvariable datatype; set localvariable = parameter2; select fields from table where field1 = parameter1; end // delimiter ;
The ironic thing of course is that once I realized what was happening I found mention of it in the reference manual here :-). Another thing that is mentioned that \ should not be used as a delimiter since it is an escape character in mySQL.