automatically placing results of a called procedure into a select statement

Question

I'm playing with some code from an article written by Peter Brawley found here on page 6 of the pdf. I'm trying to figure out how to automate it so that the result of the procedure is automatically placed in the select query. Right now what I am doing is calling the procedure, exporting the result into a text file, going to the text file manually (point click with mouse), copying the result and pasting it into a select statement. I haven't been able to figure out how to either insert the select statement into the procedure, or put the procedure into a table in my database or variable that I can call from the select statement. Any ideas?

Here is the sample code from Peter Brawley, that I've been trying to automate:

use database;
DROP PROCEDURE IF EXISTS writesumpivot;
DELIMITER |
CREATE PROCEDURE writesumpivot(
    db CHAR(64), tbl CHAR(64), pivotcol CHAR(64), sumcol CHAR(64)
)
BEGIN
DECLARE datadelim CHAR(1) DEFAULT '"';
DECLARE comma CHAR(1) DEFAULT ',';
DECLARE singlequote CHAR(1) DEFAULT CHAR(39);
SET @sqlmode = (SELECT @@sql_mode);
SET @@sql_mode='';
SET @pivotstr = CONCAT( 'SELECT DISTINCT CONCAT(', singlequote,
                    ',SUM(IF(', pivotcol, ' = ', datadelim, singlequote,
                    comma, pivotcol, comma, singlequote, datadelim,
                    comma, sumcol, ',0)) AS `',
                    singlequote, comma, pivotcol, comma, singlequote, '`',
                    singlequote, ') AS sumpivotarg FROM ', db, '.', tbl, 
                    ' WHERE ', pivotcol, ' IS NOT NULL' );
-- UNCOMMENT TO SEE THET MIDLEVEL SQL:
-- SELECT @pivotstr;
PREPARE stmt FROM @pivotstr;
EXECUTE stmt;
drop prepare stmt;
SET @@sql_mode=@sqlmode;
END
|
DELIMITER ;
call writesumpivot('database', 'table', 'pivotcol','sumcol');

Then the Select statement is as follows:

SELECT
    infoField
    [results of the call]
FROM
    database.table
GROUP BY infoField;

Assuming I've ran the call, exported the results, copied them and pasted them into the select statement, my personal results of the call in the SELECT query would look something like this:

SELECT 
    infoField
    ,SUM(IF(pivotcol = "Yellow",sumcol,0)) AS `Yellow`
    ,SUM(IF(pivotcol = "Red",sumcol,0)) AS `Red`
    ,SUM(IF(pivotcol = "Purple",sumcol,0)) AS `Purple`
    ,SUM(IF(pivotcol = "Orange",sumcol,0)) AS `Orange`
    ,SUM(IF(pivotcol = "Green",sumcol,0)) AS `Green`
    ,SUM(IF(pivotcol = "Blue",sumcol,0)) AS `Blue`
    ,SUM(IF(pivotcol = "White",sumcol,0)) AS `White`
FROM database.table
GROUP BY infoField;

Running the above select statement gives me the pivot table that I need. I'm tryig to figure out how to incorporate this into a website, which is why it needs to be automated.

I tried inserting a create table, and then reference the table, but didn't get desired resluts. Edited the last section of the PROCEDURE as follows:

--SELECT @pivotstr;
DROP TABLE IF EXISTS temp2;
CREATE TABLE IF NOT EXISTS temp2(sumpivotarg varchar(8000));
PREPARE stmt FROM @pivotstr;  
...

changed call and select as follows:

call writesumpivot('database','table','pivotcol','sumcol');
insert into temp2(sumpivotarg) values(@pivotstr);

SELECT 
   table.infoField, temp2.sumpivotarg
FROM table, temp2
GROUP BY infoField

Results from this were the generic code rather than summing the contents of the cells in the database. it looks something like this:

infoField | sumpivotarg <-- Col Headings

123 | SELECT DISTINCT CONCAT('Sum(if(pivotcol=",pivotcol",sumcol,0)) AS'pivotcol,'')..

124 | SELECT DISTINCT CONCAT('Sum(if(pivotcol=",pivotcol",sumcol,0)) AS'pivotcol,'')..

125 | select DISTINCT CONCAT('Sum(if(pivotcol=",pivotcol",sumcol,0)) AS'pivotcol,'')..

Answer 1

You could create a temp table in your DB. Use SQL insert to insert data into temp table as the result of the stored procedure execution. Afterwards you could use that temp table inside your select statement.

Here's an answer that shows how to do that:

Use result set of mysql stored procedure in another stored procedure

Just to mention a similar question:

MySQL How to INSERT INTO temp table FROM Stored Procedure