how to export and import stored procedures from phpmyadmin

Question

Can you guys help me out regarding stored procedures. When I export stored procedure from phpmyadmin, It given as

CREATE DEFINER=`root`@`localhost` PROCEDURE `c4mo_get_cities_prc`(IN `p_state_code` VARCHAR(3), IN `p_country_code` VARCHAR(3), IN `p_language_code` VARCHAR(3))
    NO SQL
BEGIN

SELECT city_name, city_code
FROM `c4mo_cities` 
WHERE enabled = 'Y' 
AND language_code = p_language_code
AND state_code = p_state_code
AND country_code = p_country_code;

END

And when I import it from phpmyadmin, it giving error as

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 13 

Answer 1

Copy the exported stored procedure to file e.g. myprocedure.sql. Now modify that .sql file with following: 1) Remove DEFINER=root@localhost 2) At the beginning of stored procedure add delimiter definition. and at the end reset delimiter.

e.g.

`DELIMITER $$
`CREATE  PROCEDURE `c4mo_get_cities_prc`(IN `p_state_code` VARCHAR(3), IN 
`p_country_code` VARCHAR(3), IN `p_language_code` VARCHAR(3))
NO SQL
BEGIN

SELECT city_name, city_code
FROM `c4mo_cities` 
WHERE enabled = 'Y' 
AND language_code = p_language_code
AND state_code = p_state_code
AND country_code = p_country_code;

END $$
DELIMITER ;

After that import stored procedure just like any other database you import by selecting .sql file.

Thank you