CODEWITHSUNDEEP
mysqlsqlstored-procedures
DBA108642
DBA108642

Reputation: 2112

MySQL pass a variable string argument to stored procedure variable

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?

Upvotes: 2

Views: 2323

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175646

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');

Upvotes: 3

Related Questions