Stored Procedure with a parameter intended to test membership in list does not work as expected
The following code shows the problem, as I see it. The "SELECT * FROM Beds WHERE Beds.Block IN (p_blocks);" is given a string parameter value for instance 'BA','BB'. I would expect this to work as if I had "IN ('BA','BB')" in the code, but it does not. MariaDB appears to want an array, but is passed a non-array text. I can make it work by building the statement as shown below at the top. But this is a messy hack, and I'd like to avoid it especially as the intended use is a lot more complicated than this example.
Can anybody tell me the proper way to do this? Thank you!
CREATE DEFINER=`root`@`%` PROCEDURE `get_beds_for_blocks`( IN `p_blocks` TEXT ) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN SET @select_beds = CONCAT("SELECT * FROM Beds WHERE Beds.BLock IN (", p_blocks, ")"); PREPARE stmt FROM @select_beds; EXECUTE stmt;
SELECT * FROM Beds WHERE Beds.Block IN (p_blocks); END
--------- Adrian Stephens, Cambridge, UK