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

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.