Is there an alternative to "Create or replace procedure" in MySQL?

Question

Is there an version of "create or replace procedure" for MySQL? I can't seem to do this or script the dropping of the procedure if exists before recompiling without getting an error message that the stored procedure exists.

DELIMITER $$

-- would love to be able to drop procedure if exists db.sp_tmp_90days;  
-- or use "create or replace"

create procedure db.sp_tmp_90days()

BEGIN
drop table db.tmp_90days;

create table db.tmp_90days ( 
    user_name varchar(128), 
    first_name varchar(50), 
    last_name varchar(50), 
    system varchar(10), 
    last_login datetime 
);

alter table db.tmp_90days add index idx_user_name(user_name);
alter table db.tmp_90days add index idx_system(system);
alter table db.tmp_90days add index idx_last_login(last_login);

insert into db.tmp_90days (user_name, first_name, last_name, system, last_login)
    SELECT
        [...]
END $$

score 33 · Accepted Answer · answered Jan 15 '16 at 00:31

33

This is the syntax to drop it if it exists

DROP PROCEDURE IF EXISTS db.sp_tmp_90days;

answered Jan 15 '16 at 00:31

Rohit Gupta

1,626
6
17
19

9

Could also mention that MariaDB, a "drop-in replacement" for MySQL, does support the CREATE OR REPLACE PROCEDURE syntax since version 10.1. – dbdemon Jan 01 '18 at 20:57
@dbdemon thanks that's helpful! – amatusko Feb 10 '21 at 13:34
can we use create or replace with definer? – Mohamad May 09 '22 at 20:07
FYI, I rejected this edit since SQL syntax highlighting is automatically active for most questions - tags may automatically apply it; check the bottom of this page. – Glorfindel Feb 26 '23 at 18:40
@Glorfindel, I was not sure if it was. I waited for it to colour up and it didn't, thats when I applied the edit. – Rohit Gupta Feb 27 '23 at 00:22

Is there an alternative to "Create or replace procedure" in MySQL?

1 Answers1