Deal with Stored Procedures using phpMyAdmin

March 2, 2012 | SQL

MySQL web interface
phpMyAdmin MySQL web interface doesn’t contain any editor to manipulate or create stored procedure like all other IDEs. But you can create/modify the Stored Procedure using the queries.

Example query for creating Stored Procedure ‘SP_INS_MEMBER’ is:

DELIMITER $$
 
DROP PROCEDURE IF EXISTS `DB_NAME`.`SP_INS_MEMBER`$$
 
CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_INS_MEMBER`(IN subProcessId VARCHAR(100), IN storeId VARCHAR(100), IN regNum VARCHAR(100))
BEGIN
 
    DECLARE b VARCHAR(100) DEFAULT '--RET Trans--';
 
    INSERT INTO MEMBER (SUB_PROCESS_ID, STORE_ID, REG_NUM, TRANS_NUM) VALUES(subProcessId, storeId, regNum, b);
 
    SELECT SUB_PROCESS_ID, STORE_ID, REG_NUM, TRANS_NUM FROM MEMBER WHERE SUB_PROCESS_ID = subProcessId;
 
END$$
 
DELIMITER ;

Copy and paste the above query in your phpMyAdmin‘s SQL tab.
Note that the table names may change according to your available tables.
If you dont want to mess up with any tables, just replace the queries between BEGIN and END$$ with the following query:

SELECT 'Hello World!';

Remember first line is very important (i.e.) selecting of delimiter. Prefer other than ; as your delimiter.

If you want to see the created Stored Procedure, execute the following query:

SHOW CREATE PROCEDURE DB_NAME.SP_INS_MEMBER

If you are viewing only partial text, then click on ‘Options‘ link just above ‘Procedure‘ column and select ‘Full Texts

Tags: , ,

Leave a Reply

If you have a Tip, Comment, Article or anything you want to share, please send it to: i@poisure.com