How to pass a string as a parameter MySQL to a stored procedure used in an IN clause?

Question

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

Answer 1

A sample:

CREATE TABLE test 
SELECT 1 id, 123.456 val UNION ALL
SELECT 2, 234.567 UNION ALL
SELECT 3, 345.678;

CREATE PROCEDURE test (IN criteria TEXT)
SELECT *
FROM test
WHERE FIND_IN_SET(test.val, criteria);

CALL test('123.456,234.432,345.678');

id	val
1	123.456
3	345.678

fiddle

---

It works, but it increased the sampling time significantly. – WarmingZ

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.

CREATE TABLE test 
SELECT 1 id, 123.456 val UNION ALL
SELECT 2, 234.567 UNION ALL
SELECT 3, 345.678;

CREATE PROCEDURE test (IN criteria TEXT)
SELECT test.*
FROM test
JOIN JSON_TABLE( CONCAT('[', criteria, ']'),
                 '$[*]' COLUMNS (val DECIMAL(10,3) PATH '$')
                 ) jsontable USING (val);

CALL test('123.456,234.432,345.678');

id	val
1	123.456
3	345.678

fiddle