mySQL Delimiters

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 exists `database``stored procedure` //
create procedure `database``stored procedure`
(parameter1 datatype,
parameter2 datatype)
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.

4 comments on “mySQL Delimiters

  1. Pingback: How to: Hidden Features of MySQL | SevenNet

  2. but here it is generating error
    plz see it

    DROP TRIGGER IF EXISTS `cc_card_serial_set`;
    CREATE TRIGGER `cc_card_serial_set` BEFORE INSERT ON `cc_card`
    UPDATE cc_card_seria set value=value+1 where id=NEW.id_seria ;
    SELECT value INTO @serial from cc_card_seria where id=NEW.id_seria ;
    SET NEW.serial=@serial;

    Enterprise Solutions

  3. I’ve run into this problem before as well. Changing the delimiter to // is useful not only for stored procedures but for any time you want to run a bunch of SQL statements. Say you have to do a one time fix that requires multiple UPDATES and INSERTS… instead of a one at a time approach, just run them all together and change the delimiter.

    This works as well if and when you use
    phpMyAdmin (very useful tool). If you run code through this web management tool, you can also change the delimiter here as well to run multiple statements.

    (my site will be down temporarily today for server maintenance)

Comments are closed.