At my wits end trying to create my very 1st MySQL stored procedure

Question

I am trying to create a stored procedure in MySQL 5.5.8. using connector 8.0.13. Once this procedure is debugged, it will have to be embedded into a c#.net application, to be created on the fly every time the program is set up. For now, I am doing it manually.

For someone like me, with MS SQL, Oracle, and DB2 background, this is challenging. I honestly read MySQL dev docs and googled to get a hang of MySQL SQL syntax. What I gather is that:

• I have to change delimiter to something like //
• I should use if not exists just in case
• Parameters do not start with @, and they have to be parenthesized
• I end the block with the temporary delimiter // and restore it to ; afterwards

This is what I arrived at:

delimiter //
create procedure if not exists `logging`.`logEntry`(
   in hostName varchar(512)
  ,in entryDateTime datetime
  ,in entryText varchar(1024)
  ,out return_value int
)
begin
  insert into `logging`.`log` (hostName, entryDateTime, entryText) select hostName, entryDateTime, entryText;
  set return_value = last_insert_id();
  select return_value;
end//
delimiter ;

When I run the above code in SquirrelSQL, the server throws the following error:

Error: 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 'if not exists loging.logEntry`( in hostName varchar(512) ,in entryDat' at line 1 SQLState: 42000 ErrorCode: 1064

Here is what I tried:

• Ran without DELIMITER
• Removed if not exists
• Removed backquotes around entity names
• Inserted values() instead of select even though the standalone insert works

I know that this is something totally obvious for a seasoned MySQL developer, but it escapes someone like me with a different background. Am I using any functionality that does not exist in 5.5.8 or are there bugs in my code?

Answer 1

When I gave up on creating the stored procedure through SquirrelSQL and installed MySQL GUI Tools, then I was able to create the procedure with the exact same source, by using Catalogs, selecting my schema, switching to the Stored procedures tab, and clicking Create Stored Proc button at the very bottom.

Answer 2

Check the manual for the correct syntax.

1064 is pointing exactly at "if not exists"; this syntax is not available in MySQL.