MySQL pass a variable string argument to stored procedure variable

Question

I have the following stored procedure:

CREATE DEFINER=`sleuser`@`%` PROCEDURE `PCDD`(
in ProjectID int,
in MonthName varchar(50),
in ServiceCode varchar(50),
in ProjectName varchar(50)
)
BEGIN

SET @PCProjID = ProjectID;
SET @PCSN1 = "020." + ServiceCode + ".000";
SET @Month = MonthName;
SET @ImpCostID = ProjectName;
SET @ImpCostTask1 = "020." + ServiceCode + ".000";

SELECT 
project.project_id,
'FP' as Phase,
ImportCost.OriginalCommitments,
ImportCost.ApprovedCommitmentChanges,
sum(RegisteredChangeOrders) + sum(OriginalContractPrice) as CurrentAssigned, 
sum(ProjectCostBudget.PendingChangeOrders) as PendingScopeChanges,

FROM `RCLY-DEV`.ProjectCostBudget

inner join project on project.project_id = 
ProjectCostBudget.ProjectID
inner join ImportCost on ImportCost.ProjectID = project.pmis
where ImportCost.ProjectID = @ImpCostID and 
ImportCost.Task = @PCSN1  and  
ProjectCostBudget.ProjectID = @PCProjID and 
ProjectCostBudget.ServiceNumber = @ImpCostTask1

which i call using:

call PCDD(2,'September%2018','0000','RLCY-BB-01')

Where '0000' needs to vary from '0000' to '6000'. When I run the SP for '0000' it returns the expected results, but when I change it to anything else it just returns all nulls. I tried updating @PCSN1 and @ImpCostTask1 to:

SET @PCSN1 = ("020.", ServiceCode, ".000");
SET @ImpCostTask1 = ("020.", ServiceCode, ".000");

But i get the error

"Operand should contain 1 column(s).

What am i doing wrong here? Why does it work for one ServiceCode but not the others?

Lukasz Szozda · Accepted Answer · 2018-09-07 14:18:50Z

3

You need to use ' enqoute string literal and CONCAT instead of '+' for string concatenation:

SET @PCSN1 = "020." + ServiceCode + ".000";
=>
SET @PCSN1 = CONCAT('020.', ServiceCode, '.000');

Same for:

SET @ImpCostTask1 = "020." + ServiceCode + ".000";
=>
SET @ImpCostTask1  = CONCAT('020.', ServiceCode, '.000');

answered Sep 7, 2018 at 14:18

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

Collectives™ on Stack Overflow

MySQL pass a variable string argument to stored procedure variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related