How to Alter a stored procedure in mysql

Question

How to Alter a stored procedure in Mysql.

DROP PROCEDURE IF EXISTS sp_Country_UPDATE; 
CREATE PROCEDURE sp_Country_UPDATE 
  ( IN p_CountryId int, 
    IN p_CountryName nvarchar(25), 
    IN p_CountryDescription nvarchar(25), 
    IN p_IsActive bit, 
    IN p_IsDeleted bit ) 
  UPDATE 
    Country 
  SET 
    CountryName = p_CountryName , 
    CountryDescription=p_CountryDescription, 
    IsActive= p_IsActive, 
    IsDeleted=p_IsDeleted 
  WHERE 
    CountryId = p_CountryId ;

How to alter this Stored Procedure?

Answer 1

If you mean you want to edit the Procedure, then you can't according to the MySQL docs:

This statement can be used to change the characteristics of a stored procedure. More than one change may be specified in an ALTER PROCEDURE statement. However, you cannot change the parameters or body of a stored procedure using this statement; to make such changes, you must drop and re-create the procedure using DROP PROCEDURE and CREATE PROCEDURE.

The Alter syntax lets you change the "characteristics" but not the actual procedure itself

http://dev.mysql.com/doc/refman/5.0/en/alter-procedure.html

Here's an example of creating, Altering (the comment) then dropping and recreating:

DROP PROCEDURE myFunc;

DELIMITER //

CREATE PROCEDURE myFunc ()
COMMENT 'test'
BEGIN
SELECT 5;
END //

DELIMITER ;

ALTER PROCEDURE myFunc
COMMENT 'new comment';

CALL myFunc();

DROP PROCEDURE myFunc;

DELIMITER //

CREATE PROCEDURE myFunc ()
COMMENT 'last time'
BEGIN
SELECT 6;
END //

DELIMITER ;

CALL myFunc();

The above CALL myFunc() statments would return 5 and then 6.

Viewing the stored procedure would show a comment of "test", "new comment" or "last time" depending on when you viewed the Procedure body (I'm not sure how to view the comments via the CLI but I can see them in the functions tab in Navicat)

Answer 2

ALTER PROCEDURE proc_name [characteristic ...]

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }