Shanthanu 365
Shanthanu 365

Reputation: 1

MySQL - Stored Procedure

I need to fetch data from the stored procedure and present it in a dashboard. So the background for this is.

For every select query in the Stored procedure will return only one record I've around 10 queries like that. Apart from that I've also a query which will return say some 5 rows in the same stored procedure.

I want this 5 records to be presented as 1 line along with the other queries in the same stored procedure.

Example:

CALL dashboard_proc() Will return

Date       Amount    total_orders
1-1-2017   500.00    50

A part from above output I want to append another select query to the same stored procedure (dashboard_proc). The other query which will return

Place      Total
Hyderabad    100
Bangalore    100
Chennai      200
Mumbai       100

And I need below

Date      Amount  total_orders  Hyderabad  Bangalore  Chennai  Mumbai
1-1-2016  500.00     50            100       100        200      100

Upvotes: 0

Views: 61

Answers (1)

Rohit Kumar
Rohit Kumar

Reputation: 806

Based on your inputs:

  1. First Part of the report has only one record so I have used the below structure
 CREATE TABLE `ResultPart1` (
  `ReportDate` date NOT NULL,
  `Amount` float DEFAULT NULL,
  `total_orders` int(11) DEFAULT NULL
)

insert into ResultPart1 select '2017-01-01', 500.00 , 50;

2. Second Part of the report will have distinct Places, here the places used are single word and no keyword used.

 CREATE TABLE `ResultPart2` (
       `Place` varchar(50) NOT NULL,
      `Total` int(11) DEFAULT NULL
    ) 
insert into  ResultPart2()
Select 'Hyderabad',    100 union 
Select 'Bangalore' ,   100 union 
Select 'Chennai'    ,  200 union 
Select 'Mumbai'      , 100;

3. Create Stored Procedure

CREATE PROCEDURE `new_procedureDashboard`()
BEGIN

Declare vPlaceCount int;
Declare vquery varchar(1000);
declare vPlace varchar(1000);
declare vPlaceTotal int;
-- select * from ResultPart1;
-- Select * from ResultPart2;

CREATE TEMPORARY TABLE IF NOT EXISTS Table_ResultPart2 
(
   Id int NOT NULL AUTO_INCREMENT,
   Place varchar(100),
   PlaceTotal int ,
   PRIMARY KEY (id)     
);
insert into Table_ResultPart2(Place,PlaceTotal)
Select distinct Place,Total from ResultPart2;

SET vPlaceCount=(Select count(*)from Table_ResultPart2);
WHILE vPlaceCount>0 DO
        SET vPlace=(Select Place from Table_ResultPart2 where Id=vPlaceCount);
        SET vPlaceTotal=(Select PlaceTotal from Table_ResultPart2 where Id=vPlaceCount);
        SET vquery=concat("", "alter table ResultPart1 add " ,vPlace ," int ;");
        Select vquery into @AddColumn;
        PREPARE stmt FROM  @AddColumn;
        -- Select @AddColumn;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SET vquery="";

       --   Select vPlace,vPlaceTotal;
        -- Update 
        SET SQL_SAFE_UPDATES = 0;
        SET vquery=concat("", "update ResultPart1 SET " ,vPlace ," = " ,vPlaceTotal ," where 1=1 ;");

        Select vquery into @UpdateColumn;
        -- select @UpdateColumn;
        PREPARE stmt1 FROM  @UpdateColumn;
        -- Select @AddColumn;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;
        SET SQL_SAFE_UPDATES = 1;



        SET vPlaceCount=vPlaceCount-1;
        SET vPlace="" ;
END WHILE;
Select * from ResultPart1;
drop TEMPORARY TABLE IF EXISTS Table_ResultPart2;
END

Explanation: Added the required column in the main resultant table using loop, and updated the values of the newly place(added) column using the loop. You will require to make changes in your stored procedure as I have used permanent tables (ResultPart1 and ResultPart2 ) .

4. Result enter image description here

Alter Statements to drop column Just in case you want to rerun the stored procedure.

alter table ResultPart1 drop column Hyderabad;
alter table ResultPart1 drop column Bangalore;
alter table ResultPart1 drop column Chennai;
alter table ResultPart1 drop column Mumbai;

Upvotes: 1

Related Questions