StoredProcedure result into a temporary table in MySQL procedure
You can create and populate temporary table in the procedure. Then you can refer to this table by its name from the another procedure, but you have to use the session where temporary table was created. Temporary tables are session scoped objects.
Example:
DELIMITER $$
CREATE PROCEDURE ABC()
BEGIN
CREATE TEMPORARY TABLE tmpEnt SELECT 1;
END$$
CREATE PROCEDURE XYZ()
BEGIN
INSERT INTO tmpEnt VALUES (100);
END$$
DELIMITER ;
CALL ABC; -- it will create temp. table
CALL XYZ; -- it will insert one more record into temp. table
SELECT * FROM tmpEnt;
+-----+
| 1 |
+-----+
| 1 |
| 100 |
+-----+
I used stored procedures instead of functions.
Niladri
Updated on June 24, 2022Comments
-
Niladri almost 2 years
I have a stored procedure/ routine "ABC" which returns me a table with few rows of columns.
In another SP/Routine "XYZ" I want to compare the resultset returned by "ABC" with a table in the db.
But i have hit a roadblock where I am not able to find a way to call the SP "ABC" from within "XYZ". Is there any way I can populate a temporary table by calling "ABC" from "XYZ".
I am very new to MySQL, and am quite sure this is possible in SQL Server and i am now clueless to how i can proceed further. please help me identify where i am making a mistake in understanding my process.