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 :-
drop procedure if exists `database``stored procedure` //
create procedure `database``stored procedure`
declare localvariable datatype;
set localvariable = parameter2;
select fields from table where field1 = parameter1;
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.