CODEWITHSUNDEEP
mysqlstored-proceduresexists
Frank
Frank

Reputation: 7585

How to say create procedure if not exists in MySQL

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?

Upvotes: 39

Views: 46875

Answers (5)

da Bich
da Bich

Reputation: 536

mySQL 8 allows to check for existence within the create statement using IF NOT EXISTS. https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html

CREATE
    [DEFINER = user]
    PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = user]
    FUNCTION [IF NOT EXISTS] sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

Upvotes: 3

Andreas Heissenberger
Andreas Heissenberger

Reputation: 788

Just call the procedure

CALL my_procedure();

if you get the specific error

PROCEDURE yourDB.my_procedure does not exist

you can now react to "Error Code : 1305" and create the missing procedure:

CREATE PROCEDURE my_procedure() BEGIN ... END

Upvotes: -1

Cory Klein
Cory Klein

Reputation: 55690

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

DROP PROCEDURE IF EXISTS my_procedure;
CREATE PROCEDURE my_procedure()

Upvotes: 47

Ben English
Ben English

Reputation: 3918

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;

Upvotes: 13

robertvoliva
robertvoliva

Reputation: 902

It doesn't exist. We had to write a stored procedure that mimics the same functionality. Basically we create stored procedures by calling a stored procedure that does the "if exists" check.

Upvotes: 5

Related Questions