Drop procedure if exists in mysql

Question

Hi i am trying to create a mysql script that I can run whenever I need to update my database. The script creates a table and then executes some stored procedures.

DELIMITER $$

CREATE TABLE IF NOT EXISTS tbl_name (
    col1 bigint(20) NOT NULL AUTO_INCREMENT,
    col2  varchar(255) NOT NULL,
    col3 varchar(64) NOT NULL,
    col4 datetime DEFAULT NULL,
    PRIMARY KEY (`col1 `),
    UNIQUE KEY col2  (`col2`)
) ENGINE=InnoDB AUTO_INCREMENT=572 DEFAULT CHARSET=utf8$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `myproc`(IN username 
VARCHAR(255))
BEGIN
    DECLARE var1 VARCHAR(64);
    DECLARE expirationDate DATETIME;

    SET var1 = 12345;
    SET expirationDate = DATE_ADD(NOW(), INTERVAL 30 SECOND);

    REPLACE INTO tbl_name (col2, col3, col4) VALUES (someval, var1, expirationDate);
END$$

DELIMITER ;

When I ran the script first time, it created the table and executed the stored procedure in MySQL Workbench. When I ran the same thing second time, I got the error 1304 procedure already exists.

I looked online here about dropping the procedure and then create again. But when I entered the below command before creating the procedure, i got an error on CREATE command with code 1064.

DROP PROCEDURE IF EXISTS myproc;
CREATE DEFINER=`root`@`localhost` PROCEDURE `myproc`(IN username 
    VARCHAR(255))
.
.
.

I am very new to mysql and not sure how to execute the procedure if it already exists.

Any help would be appreciated.

Solution

Since you changed to DELIMITER $$ you need to use that delimiter at the end of each statement until you change it back.

DROP PROCEDURE and CREATE PROCEDURE are separate statements, and each requires its own statement delimiter.

DROP PROCEDURE IF EXISTS myproc $$

Note the delimiter at the end of the line above.

CREATE DEFINER=`root`@`localhost` PROCEDURE `myproc`(IN username 
    VARCHAR(255))
.
.
.
END $$

And another delimiter at the end of the whole CREATE PROCEDURE statement.

Comments

[Naphstor]

Hey @Bill, thanks for your reply. I am already putting the delimiter at the end of each proc before changing it back to semicolon ';'

[Bill Karwin]

@Naphstor, please see my edited answer above for some clarification.