14

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

This is the syntax to drop it if it exists

DROP PROCEDURE IF EXISTS db.sp_tmp_90days;
  • 3
    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 1 '18 at 20:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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