Tags: MySQL , Stored Procedure

Few days back I wrote an article about How To Call Stored Procedure In Hibernate. After following few migration process of stored procedure. I was struggling to select data from another stored procedure within stored procedure. Finally I came up with solution after Googling few pages and reading books.

TEMPORARY table in MySQL is our wildcard in this database programming. Well TEMPORARY tables should be used carefully or you'll face few problems in MySQL. We'll discuss more about TEMPORARY tables in MySQL in my next article.

STORED PROCEDURE 1
Stored Procedure 1 is where we want to return something after processing some of our inputs.

/* Drop procedure existing procedure if exists */
DROP PROCEDURE IF EXISTS SP_CALL;
DELIMITER $
/* Creating new Stored Procedure */
CREATE PROCEDURE SP_CALL()
BEGIN
 /* Drop TEMPORARY table if exists. */
 DROP TEMPORARY TABLE IF EXISTS Results;
 /* Create TEMPORARY table */
 CREATE TEMPORARY TABLE Results(Firstname VARCHAR(200));
 
 /* Insert value in TEMPORARY table */
 INSERT INTO Results 
 SELECT Firstname FROM user_master WHERE uid = 1;
END $
DELIMITER ;

STORED PROCEDURE 2
Stored Procedure 2 is where you want to select output of Stored Procedure 1. Here you can access TEMPORARY table Results declared in SP_CALL()

/* Drop procedure existing procedure if exists */
DROP PROCEDURE IF EXISTS SP_SELECT;
DELIMITER $
/* Creating new Stored Procedure */
CREATE PROCEDURE SP_SELECT()
BEGIN
 /* Declare variable */
 DECLARE F_NAME VARCHAR(100);
 
 /* Call Stored Procedure */
 CALL SP_CALL();
 /* Select from TEMPORARY table declared in SP_CALL */
 SELECT Firstname INTO F_NAME FROM Results;
 
 /* Print value of variable */
 SELECT F_NAME;
END $
DELIMITER ;

Tags: MySQL , Stored Procedure