How to apply where condition in query which is stored in other table MySQL

Question

I have requirement where i will fetch where condition stored in other table which can be like id > 10 or amount < 100.

I am using Stored Procedure to perform some task in which i am retrieving this where condition and using it to insert some data into table. But it's not working maybe due to apostrophe is being appended at front and end.

set @whereC = (select FilterCondition from SearchLeads where `SearchLeadID` = sid);
INSERT INTO `JTemporary` 
        (`ZipID`,`FirstName`,`LastName`,`MemberSince`,
        `Address1`,`Phone`,`Email`,`CompanyName`,
        `BusPhone`,`Deleted`,`CreatedBy`,`CreateDate`,
        `UpdatedBy`,`UpdateDate`)
  select `ZipCode`,`FirstName`,`LastName`,`AddDate`,
        `AddressLine1`,`HomePhone`,`HomeEmail`,`Employer`,
        `BusinessPhone`,'N',loginUserID,now(),
        loginUserID,now() 
    from membertrans where @whereC;

This isn't working. When i apply directly by copying that condition and putting in place of variable it works, But doesn't work with variable.

How to achieve this?

Surely your WHERE should be `WHERE \`somecolumn\` = @whereC` Not just `where @whereC` — RiggsFolly, Oct 11 '18 at 11:17
actually i am storing whole where condition which is generated on other page when user search any filter criteria and can save this search. So if user always wants to see price < 10000 then s/he will save this criteria and on other page when user selects this, i insert record or perform some task with this where condition which was stored previously — ravi, Oct 11 '18 at 11:19
Oh right I missed that, sorry. No idea if that will work, better wait for a SQL guru @Barmar are you there — RiggsFolly, Oct 11 '18 at 11:27
Unfortunately you can't just embed a chunk of SQL statement like that - you will need to build an SQL statement into a variable, and then execute it. As all of this is happening in a stored procedure, that should be fine to do. I'll do you a proper example in an answer later if no-one else has, no time now. — MandyShaw, Oct 11 '18 at 11:48
Thanks.. waiting for it because i'm not getting any other solutions as of now. Waiting for what SQL Guru @Barmar has to say about this issue. — ravi, Oct 11 '18 at 11:53

score 1 · Accepted Answer · answered Oct 11 '18 at 20:08

Here's an example that works on my mysql database.

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(whereClause varchar(512))
BEGIN
SET @str = concat('SELECT * from mytable where ',whereClause);
PREPARE myquery FROM @str;
EXECUTE myquery;
END

I can call the above passing in whatever where clause I like, e.g.

call test ('col1 between 5 and 10 and col2=''a bit of text''')

The key here is obviously the SET/PREPARE/EXECUTE, which is what you'll need to do.

I used this question Is it possible to execute a string in MySQL? to derive some of the above, since all my own examples turned out to be in SQL Server.

score 0 · Answer 2 · answered Oct 11 '18 at 11:43

you have not put any column in where condition to match with select query column FilterCondition. Eg.

set @whereC = (select FilterCondition from SearchLeads where `SearchLeadID` = sid);
INSERT INTO `JTemporary` 
        (`ZipID`,`FirstName`,`LastName`,`MemberSince`,
        `Address1`,`Phone`,`Email`,`CompanyName`,
        `BusPhone`,`Deleted`,`CreatedBy`,`CreateDate`,
        `UpdatedBy`,`UpdateDate`)
  select `ZipCode`,`FirstName`,`LastName`,`AddDate`,
        `AddressLine1`,`HomePhone`,`HomeEmail`,`Employer`,
        `BusinessPhone`,'N',loginUserID,now(),
        loginUserID,now() 
    from membertrans where **filterColumn ** IN (or any other clause) @whereC;

I think that is the issue, Try this may this will help you.

Please read question.. I am fetching whole condition with filter column from other table. — ravi, Oct 11 '18 at 11:53

How to apply where condition in query which is stored in other table MySQL

2 Answers2