Evaluate a string in a MySQL stored procedure

Question

I have a stored procedure that I would like to pass a string to, to be evaluated in the query. I will be building a string in php, based on query parameters that exist. So, my string might be col1 = val1 or col1 = val1 AND col2 = val2 AND col3 = val3, ect. Then in my stored procedure, I would do something like this: SET @s = CONCAT('SELECT * from tablename WHERE ' , string); PREPARE stmt FROM @s; EXECUTE stmt; I have tried some different ways of doing this, with no success. Is this possible? Thanks.

Answer 1

Without seeing your entire proc, that does seem like something that should work. Make sure you specify 'READS SQL DATA' in the CREATE clause for your stored proc, ie:

CREATE PROCEDURE `foo`(IN bar varchar(100))
READS SQL DATA
BEGIN
-- blah blah blah
END

I'm doing what you are trying to do in MySQL, so you're just missing some little thing somewhere.

Answer 2

Your code it valid, you might have an error though. Try debugging it by replacing PREPARE stmt FROM @s; EXECUTE stmt;

with

SELECT @s;

then make sure the output is valid by running it, its possible your building @s incorrectly.