30

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 $$

1 Answer 1

33

This is the syntax to drop it if it exists

DROP PROCEDURE IF EXISTS db.sp_tmp_90days;
5
  • 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
    Commented Jan 1, 2018 at 20:57
  • @dbdemon thanks that's helpful!
    – amatusko
    Commented Feb 10, 2021 at 13:34
  • can we use create or replace with definer?
    – Mohamad
    Commented May 9, 2022 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
    Commented Feb 26, 2023 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. Commented Feb 27, 2023 at 0:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.