Adding DELIMITER to Stored Procedures

I have read several posts noting that HeidiSQL automatically isolates the stored procedure delimiters from the default one. My problem is that an external ODBC program needs the DELIMITER to be explicitly set. I see what needs to be done to the procedure definition, but obviously it can't be done in the GUI and the cute little SQL Delimiter icon on the tool bar is not active in the procedure editing GUI.

All the references to this that I can find assume the reader understands how to edit the stored procedure without using the GUI interface. I flat out admit that I am not at that point. I tried generating the SQL code and pasting it in a new query and running it, but that seems like it must not be the correct way.

What is the correct way to generate or edit a stored procedure from outside of the GUI. If you would be so kind, maybe a few words about why what you are suggesting works would be appreciated.

You can use the "DELIMITER ..." client command to set a different delimiter in HeidiSQL. This is what HeidiSQL itself does when you export a trigger for example:

DELIMITER //
CREATE DEFINER=`root`@`localhost` TRIGGER `trtest` BEFORE INSERT ON `foo` FOR EACH ROW BEGIN
  SET NEW.bar=TRIM(NEW.bar);
  END//
DELIMITER ;

ansgar,

Thanks for your reply, what I don't understand is how to get out of the GUI mode and into the line editor mode to be able to write the code that you show. There doesn't seem to be a way from the standard stored procedure method to insert anything prior to the BEGIN structure.

Should I just open new query tab or is there a better interface in HS for that?

You just want to use a query tab for that. Here's another example:

DELIMITER //
CREATE FUNCTION `routinetest`(`foo` INT)
RETURNS tinyint(1) LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA SQL SECURITY DEFINER
BEGIN
    RETURN 1;
END //
DELIMITER ;