MySQL stored procedure loop through variables and insert into temporary table

Question

I have a stored procedure that runs a select statement that returns one row and inserts the contents into a temporary table. The select statement has 6 conditional statements in a where clause and I essentially need to loop through 3 additional sets of criteria and insert those results into the temp table. Here is what I have so far:

CREATE DEFINER=`sleuser`@`%` PROCEDURE `CostDashboard`()
BEGIN

create temporary table TempTable (
ProjectID int, 
Phase varchar(100), 
OriginalCommitments float, 
ApprovedCommitmentChanges float, 
CurrentAssigned float, 
PendingScopeChanges float,
EAC float,
PercentComplete float
);

insert into TempTable(
SELECT project_id,
'FP' as Phase,
OriginalCommitments,
ApprovedCommitmentChanges,
OriginalCommitments+ApprovedCommitmentChanges as CurrentAssigned,
sum(ProjectCostBudget.PendingChangeOrders) as PendingScopeChanges

sum(ProjectCost.CurrentWorkCompleted) + 
sum(ProjectCost.EstimateToComplete) as EAC,

(sum(ProjectCost.CurrentWorkCompleted) + 
sum(ProjectCost.EstimateToComplete) / 
(sum(ProjectCostBudget.OriginalContractPrice + 
ProjectCostBudget.RegisteredChangeOrders))) as PercentComplete

FROM `RCLY-DEV`.project

inner join ImportCost on ImportCost.ProjectID = project.pmis
inner join ProjectCostBudget on ProjectCostBudget.ProjectID = 
project.project_id
inner join ProjectCost on ProjectCost.ProjectID = project.project_id

where ImportCost.ProjectID = 'RLCY-BB-01' 
and ImportCost.Task = "020.0000.000"  
and ProjectCostBudget.ProjectID = 2 
and ProjectCostBudget.ServiceNumber = "020.0000.000" 
and ProjectCost.MonthYear != '' 
and ProjectCost.MonthYear like 'July%2018'
);

select * from TempTable
;

END

This works and inserts the one record with hard coded values in the where clause, but I need to run it for 3 sets of variables, so I created an additional temporary table like this:

|ImpCostID|ImpCostTask |PCBID|PCBServNum  |MonthYear|
-----------------------------------------------------
|XXY-01-01|030.0000.000|3    |030.0000.000|July%2018|
|QWY-01-01|040.0000.000|4    |040.0000.000|May%2018 |
|ZXF-01-01|040.0000.000|5    |050.0000.000|June%2018|

but I'm not sure how to assign these sets of values to variables and then loop through them. Any suggestions?

slaakso · Accepted Answer · 2018-08-30 17:10:49Z

1

Try to avoid serialization of SQL statements whenever possible (can be costly). In this case you can simply use a JOIN with the parameter table with your actual query. If you just used the temp table TempTable to hold the results for you, you do not need that either as you get all the results in one query:

CREATE DEFINER=`sleuser`@`%` PROCEDURE `CostDashboard`()
BEGIN

create temporary table query_params_tmp (
ImpCostID varchar(20),
ImpCostTask varchar(20),
PCBID int,
PCBServNum varchar(20),
MonthYear varchar(20)
);
insert into query_params_tmp values 
('XXY-01-01', '030.0000.000', 3, '030.0000.000', 'July%2018'),
('QWY-01-01', '040.0000.000', 4, '040.0000.000', 'May%2018'),
('ZXF-01-01', '040.0000.000', 5, '050.0000.000', 'June%2018');


SELECT project_id,
  'FP' as Phase,
  OriginalCommitments,
  ApprovedCommitmentChanges,
  OriginalCommitments+ApprovedCommitmentChanges as CurrentAssigned,
  sum(ProjectCostBudget.PendingChangeOrders) as PendingScopeChanges
  sum(ProjectCost.CurrentWorkCompleted) + 
  sum(ProjectCost.EstimateToComplete) as EAC,

  (sum(ProjectCost.CurrentWorkCompleted) + 
  sum(ProjectCost.EstimateToComplete) / 
  (sum(ProjectCostBudget.OriginalContractPrice + 
  ProjectCostBudget.RegisteredChangeOrders))) as PercentComplete
FROM `RCLY-DEV`.project
  join ImportCost on ImportCost.ProjectID = project.pmis
  join ProjectCostBudget on ProjectCostBudget.ProjectID = project.project_id
  join ProjectCost on ProjectCost.ProjectID = project.project_id
  join query_params_tmp qp on 
    qp.ProjectID = ImportCost.ProjectID and 
    qp.ImpCostTask = ImportCost.Task and
    qp.PCBID = ProjectCostBudget.ProjectID and
    ProjectCost.MonthYear like qp.MonthYear
GROUP BY OriginalCommitments, ApprovedCommitmentChanges;

END

edited Aug 30, 2018 at 17:10

answered Aug 30, 2018 at 16:35

slaakso

9,1452 gold badges19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DBA108642 Over a year ago

I like the thought process, but when i run this it returns 0 records.

slaakso Over a year ago

There was a typo in temp table population. You can try again. If it does not return the desired results, please provide sample data in form of a SQLFiddle.

DBA108642 Over a year ago

I actually caught that before running. I'll try to get something into a SQL Fiddle

Venkatesh R · Accepted Answer · 2018-08-30 16:12:11Z

0

if I understood your Q properly then below will resolve.

Create temporary table with identity column and then insert the data in it. This will help to loop through each record if the temp table not having unique column

--declare variable
DECLARE @ID INT, 
        @Task VARCHAR(256),
        @ProjectID INT,
        ...
        ...
        ...

SELECT @ID = MIN(ID)
FROM TemporaryTable

WHILE ISNULL(@ID, '') <> ''
BEGIN
    SELECT @Task = ImpCostTask
          ,@ProjectID = PCBID
            ...
            ...
            ...
    FROM TemporaryTable
    WHERE ID = @ID

    insert into TempTable(
    SELECT project_id,
    'FP' as Phase,
    OriginalCommitments,
    ApprovedCommitmentChanges,
    ....
    ....
    ....
    ....
    FROM `RCLY-DEV`.project
    inner join ImportCost on ImportCost.ProjectID = project.pmis
    inner join ProjectCostBudget on ProjectCostBudget.ProjectID = project.project_id
    inner join ProjectCost on ProjectCost.ProjectID = project.project_id
    where ImportCost.ProjectID = @ProjectID
    and ImportCost.Task = @Task
    and ProjectCostBudget.ProjectID = @ProjectID
    ....
    ....
    ....
    ....
    ....
    ....
    ....
    );

    SELECT @ID = MIN(ID)
    FROM TemporaryTable
    WHERE ID > @ID
END

answered Aug 30, 2018 at 16:12

Venkatesh R

5381 gold badge11 silver badges31 bronze badges

1 Comment

slaakso Over a year ago

The syntax is for MSSQL whereas the question was for MySQL

Collectives™ on Stack Overflow

MySQL stored procedure loop through variables and insert into temporary table

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related