Dynamic MySQL Where Clause in Stored Procedure

Question

I have a question and maybe its simple (for you Gurus).

I'm transposing my SQL Paging class from C# to a MySQL Stored Procedure. In my C# home-made object, the query is dynamically built based off a criteria. Example:

if(keywords is not null)
{ 
  whereClause += "WHERE description LIKE '%keywords%'"
}
if(price is not null)
{
  whereClause += "AND price = '%price%'"
}

....

string query = "SELECT col1, col2 FROM tblThreads " + whereClause

Now, my question is: How do I do a dynamic where clause in MySQL similar to this? Or rather, if they don't enter anything for those parameters, how would I tell MySQL in the Stored Procedure to skip those? IE:

SELECT col1, col2 FROM tblThreads

Would something like this work, if those parameters were null?

SELECT col1, col2 FROM tblThreads WHERE (IS NULL @keywords OR description like '%@keywords%'

??

Thanks guys.

Solution 1

The easiest way if you're allowing them to query the entire database is to just add a 1 = 1 to your statement Something like

whereClause = "WHERE 1 = 1"

if(keywords is not null)
{ 
 whereClause += "AND description LIKE '%keywords%'"
}
if(price is not null)
{
 whereClause += "AND price = '%price%'"
}

Solution 2

You can use CASE statement to check for the value of @keywords, eg.

SELECT  col1, col2 
FROM    tblThreads 
WHERE   description LIKE  CASE WHEN @keywords IS NULL 
                            THEN description
                            ELSE CONCAT('%', @keywords, '%')
                            END
        AND
        price LIKE  CASE WHEN @price IS NULL 
                            THEN price
                            ELSE CONCAT('%', @price, '%')
                            END

Comments

[Vinny Roe]

I like this one. I use this myself, for when I have to do "heavy" parsing of SQL statements, ie programmatically removing AND clauses. Allows you to 'go back to previous "AND ", and remove that line, etc. Discounting the WHERE clause makes this much easier.

[Jordan G]

What happens if all the parameters are null? Wouldn't that make for poor execution? IE: description LIKE '%%' AND price LIKE '%%' AND param3 LIKE '%%' AND ... paramX LIKE '%%'?

[Jordan G]

I saw this once before, and even though it doesn't answer my original question it is interesting. Assuming I stick to my C# object class, right now I'm trying to identify if I'm using "AND" (I have a boolean variable that is set to true the moment I use AND.) Assuming I do this, I wouldn't have to keep track of when I used my first parameter, correct? Does this have any performance penalties and does 1 = 1 cause any issues?

[Jordan G]

Just looked up some additional details here: stackoverflow.com/questions/8149142/where-1-1-statement I actually like this idea and will try implementing it. Thanks for the tip! I may also be able to finally build my stored procedure using this method as well if I decide to pursue that avenue. Thanks guys.

[Jordan G]

Ignore my last comment. If I do the 1 = 1 method, then insert a case statement afterwords with a CASE WHEN @price <> NULL THEN AND price LIKE '%price%' Should work I'm guessing... I've never tried it, so this is speculation.