Tips to change your MySQL Procedure through command shell

Drop your procedure before modifications

There is no alter command like table alter in mysql for modifying yourÂ MySQL procedure code. So you have to drop your procedure and then create the same procedure for altering your procedure. ToÂ drop the procedure, execute the following line of code in your command shell:

DROP PROCEDURE your_database.your_procedure_name;

1	DROP PROCEDURE your_database.your_procedure_name;

Note:Â Mysql ALTER PROCEDUREÂ can be used to change the characteristics of a stored procedure only, not the parameters or body of the procedure.

Change the delimiter

Normally body ofÂ the procedure consists of semicolons (;) for the end of the line (statement). To execute the procedure as a single statement, you have to change the delimiter for the console (commandÂ line interface). You can change theÂ delimiter to ‘/’ by executing the code below:

DELIMITER /

1	DELIMITER /

Execute the CREATE PROCEDURE code

Now you can type/copy your procedure code. An example procedure code is given below for your reference:

CREATE PROCEDURE `database_name`.`procedure_name` () BEGIN
    -- Getting total.
    SELECT COUNT(`id`) AS 'total' FROM `table_name`;
    
    -- Getting sum.
    SELECT SUM(`price`) AS 'sum' FROM `table_name`;
END;
/

CREATE PROCEDURE `database_name`.`procedure_name` () BEGIN

-- Getting total.

SELECT COUNT(`id`) AS 'total' FROM `table_name`;

-- Getting sum.

SELECT SUM(`price`) AS 'sum' FROM `table_name`;

END;

Note the last line of the procedure code. Your procedure will get executed only when you add the changed delimiterÂ /Â .Â If there are no issues with your code, your procedure will add successfully to the server.

Change to delimiter back to normal

You already added the procedure to the database, but make sure that you changed your delimiter back to the normal. Execute the following code for changing the delimiterÂ to the default semicolon.

DELIMITER ;

1	DELIMITER ;