How to pass an array to a MySQL stored procedure?

This post explains how to pass an array or a list of items (string, integer, etc.) to a MySQL stored procedure in order to use it within an IN operator :

/* ... */
WHERE `Column` IN (Array_Of_Items)  
/* ... */

Because MySQL doesn't have any type Array or List, we need to find a workaround : a common alternative is to consider the array as a string where each value would be separated by a comma.

Then you can concatenate the said string with the rest of your query and use it in a prepared statement.

Example :

DELIMITER $$  
CREATE PROCEDURE `UpdateTable`(list_of_ids VARCHAR(32))  
BEGIN  
   set @sql = concat("UPDATE `TABLE` SET `Column` = 'value'
                      WHERE `TABLE`.Id in (" , list_of_ids , ")");

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
END$$;  
DELIMITER ;  

And that's it ! :-)

Then you can call the procedure like this :

CALL UpdateTable('1,2,3,4,5,6,7,8,9,10');  

Or like this if you want to pass a list of strings

CALL UpdateTable("'element1', 'element2', 'element3', 'element4'");  

Easy huh ?

But what happens if we pass a string having a length longer than the maximum length authorised for list_of_ids (i.e. 32 in the above example) ?

DELIMITER $$  
CREATE PROCEDURE `Test`(list_of_ids VARCHAR(32))  
BEGIN  
   SELECT list_of_ids;
END$$;  
DELIMITER ;

CALL Test('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19');  

Absolutely nothing ... the above example returns silently '1,2,3,4,5,6,7,8,9,10,11,12,13,14', omitting a big part of the array we tried to pass, without any warning ...

To avoid this, you can add a quick preliminary check at the beginning of the procedure :

DELIMITER $$  
CREATE PROCEDURE `Test`(list_of_ids VARCHAR(32))  
test_proc : BEGIN

    # preliminary check
    IF length(list_of_ids) = 32 THEN
        SELECT 'wrong parameter' as `error`;
        leave test_proc;
    END IF;

    # do some stuff then ... 
    SELECT list_of_ids;

END$$;  
DELIMITER ;

#this will return : 'wrong parameter'
CALL Test('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19');

#this will return : '1,2,3,4,5,6,7,8,9,10'
CALL Test('1,2,3,4,5,6,7,8,9,10');  

Enjoy !

Note : this little trick can be extend to any unusual data you want to pass to a stored procedure, you could, for example, pass a table name in order to do some operations on it :

DELIMITER $$  
CREATE PROCEDURE `Example`(table_name VARCHAR(32))  
BEGIN  
    set @sql = concat("SELECT * FROM ", table_name);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
END$$;  
DELIMITER ;