How to say create procedure if not exists in MySQL

Question

I am trying to create a procedure in a MySQL database, but I want to check if it exists first.

I know how to do it for a table but when I use the same syntax for a stored procedure it doesn't compile.

Does anybody know?

Answer 1

Just drop the procedure if it does exist and then re-add it:

DROP PROCEDURE IF EXISTS my_procedure;
CREATE PROCEDURE my_procedure()

Answer 2

SELECT EXISTS(SELECT 1 FROM mysql.proc p WHERE db = 'db_name' AND name = 'stored_proc_name');

So you could do:

IF NOT EXISTS(SELECT 1 FROM mysql.proc p WHERE db = 'db_name' AND name = 'stored_proc_name') THEN
....
END IF;