CODEWITHSUNDEEP
mysqlstored-procedures
Jon Selby
Jon Selby

Reputation: 522

MySql Stored Procedure and Comma Separated Varchar with In Statement

I'm trying to create a stored procedure which will take a comma separated list as a value and push that into a select statement with an IN clause. I'm aware of find_in_set which works, but does have a performance overhead. I've also trialled a prepare statement but the problem remains.

My Example:

My parameter is sp1 and contains the value: 'valuex, valuey, valuez'.

BEGIN

    set @sql = concat('select *  from tablename WHERE assignedTo in (', sp1, ')');

    PREPARE q FROM @sql;

    execute q;

END

As expected, this throws an error since SQL will treat the value as column names. My question is how do I achieve: 'valuex','valuey','valuez' FROM 'valuex, valuey, valuez'?

Upvotes: 0

Views: 1908

Answers (2)

wchiquito
wchiquito

Reputation: 16551

An option is:

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_test`$$

CREATE PROCEDURE `sp_test`(`sp1` VARCHAR(50))
BEGIN
    SET `sp1` := REPLACE(REPLACE(`sp1`, ', ', ','), ',', '\',\'');
    SET @`query` := CONCAT('SELECT `column0`, `column1` FROM `tablename` WHERE `assignedTo` IN (\'', `sp1`, '\');');
    PREPARE `stmt` FROM @`query`;
    EXECUTE `stmt`;
    DEALLOCATE PREPARE `stmt`;
END$$

DELIMITER ;

CALL `sp_test`('valuex, valuey, valuez');
-- SELECT `column0`, `column1` FROM `tablename` WHERE `assignedTo` IN ('valuex','valuey','valuez');

Upvotes: 2

carmine
carmine

Reputation: 1655

You can use replace function:

BEGIN

    set @sql = concat("select *  from tablename WHERE assignedTo in ('", replace(sp1,",","','"), "')");

    PREPARE q FROM @sql;

    execute q;

END

Upvotes: 3

Related Questions