Dynamic MySQL Where Clause in Stored Procedure

Question

I transpose my SQL Paging class from the C# to the MySQL Stored Procedure.

In my C# home-made object, the query is dynamically built based off 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

So how can I able to do a dynamic where clause in MySQL like this? Or preferably, 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 similar to work, if those parameters were null?

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

Answer 1

The simplest way if you are allowing them to query the entire database is to simply add a 1 = 1 to your statement something like below:

whereClause = "WHERE 1 = 1"

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

Willing to Learn SQL? Sign up for the SQL Certification course by Intellipaat.