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.

Answer 1

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

Answer 2

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%'"
}