MySQL Stored Procedured apply filter using IN and the filter value is coming from a parameter-CodePudding

As the title mention, i have a problem to apply simple filter on a query result using the operator (IN) in a MySQL Stored Procedured.

The simple example of the Stored Procedured looking like this

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_example`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_example`(
    `filter_uid_value` TEXT
)
BEGIN

SELECT
    a.id
    , a.name
    , a.uid
FROM
    employee_example a
WHERE 1=1
    AND a.uid IN (filter_uid_value)

END$$
DELIMITER;

so when i call this stored procedured, for example like this

CALL sp_example("du4jgjVRJGs,oKxU3SzV8CK");

the filter is not apply, i wonder how can fix this

CodePudding user response：

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_example`(
    `filter_uid_value` TEXT
)
SELECT id, name, uid
FROM employee_example
WHERE FIND_IN_SET(uid, filter_uid_value);

DELIMITER not needed.

DELIMITER ;;

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_example`(
    `filter_uid_value` TEXT
)
BEGIN
SET @sql := CONCAT( 'SELECT id, name, uid ',
                    'FROM employee_example ',
                    'WHERE uid IN ("',
                    REPLACE(filter_uid_value, ',', '","'),
                    '")' );
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
END;;

DELIMITER ;