I have a procedure that I pass parameters to:
CALL GetSelectedCodesCount('46.12', 'yes', 'uo');
And it works, but I need to pass several different numeric values in the first parameter. Like this:
CALL GetSelectedCodesCount(" '46.12' , '46.13' ", 'yes', 'uo');
Since these parameters then fall into the IN sample and I would like to pass exactly such a tape. I've tried various combinations including sending a parameter. If I send only one numeric value everything works, but if more than one I get 0.
I also tried to work on the selectedCodes string.
SET selectedCodes = REPLACE(selectedCodes, ' ', '');
SET selectedCodes = CONCAT("'", REPLACE(selectedCodes, ",", "','"), "'");
SET selectedCodes = REPLACE(selectedCodes, '"', "' ");
And it seems to get a value that is correctly formatted for the IN statement, but I still get null :(
My procedure:
CREATE DEFINER=`***`@`%` PROCEDURE `database`.`GetSelectedCodesCount`(
IN selectedCodes TEXT,
IN primaryCondition VARCHAR(50),
IN tableName VARCHAR(50)
)
BEGIN
DECLARE result INT;
DECLARE result_uo INT;
DECLARE result_fop INT;
IF tableName = 'uo' THEN
-- Performing selection for UO
SELECT COUNT(*) INTO result_uo
FROM table1
JOIN table2 ON table2.CODE = table1.CODE
JOIN table3 ON table3.RECORD = table2.RECORD
WHERE table1.CODE IN (selectedCodes)
AND table3.STAN = 'registered'
AND (primaryCondition IS NULL OR table2.PRIMARY = primaryCondition);
SET result = result_uo;
ELSEIF tableName = 'fop' THEN
-- Performing selection for FOP
SELECT COUNT(*) INTO result_fop
FROM table1
JOIN table4 ON table4.CODE = table1.CODE
JOIN table5 ON table5.RECORD = table4.RECORD
WHERE table1.CODE IN (selectedCodes)
AND table5.STAN = 'registered'
AND (primaryCondition IS NULL OR table4.PRIMARY = primaryCondition);
SET result = result_fop;
ELSE
-- Performing selection for both types
-- Selection for UO
SELECT COUNT(*) INTO result_uo
FROM table1
JOIN table2 ON table2.CODE = table1.CODE
JOIN table3 ON table3.RECORD = table2.RECORD
WHERE table1.CODE IN (selectedCodes)
AND table3.STAN = 'registered'
AND (primaryCondition IS NULL OR table2.PRIMARY = primaryCondition);
-- Selection for FOP
SELECT COUNT(*) INTO result_fop
FROM table1
JOIN table4 ON table4.CODE = table1.CODE
JOIN table5 ON table5.RECORD = table4.RECORD
WHERE table1.CODE IN (selectedCodes)
AND table5.STAN = 'registered'
AND (primaryCondition IS NULL OR table4.PRIMARY = primaryCondition);
-- Returning the sum of results
SET result = result_uo + result_fop;
END IF;
SELECT result;
END
A sample:
fiddle
If so then provide your values list as CSV/JSON, parse in the query to the rowset (JSON_TABLE) then join to data source table.
fiddle