Dynamic MySQL Where in a Stored Procedure

Question

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

I am moving my SQL Paging class from C # to the MySQL stored procedure. In my C # home object, the query is dynamically built based on 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 to make a dynamic where clause in MySQL look like this? Rather, if they do not enter anything for these parameters, how can I tell MySQL in the Stored Procedure to skip these? IE:

 SELECT col1, col2 FROM tblThreads 

Something like this work if these parameters were empty?

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

??

Thanks guys.

Answer 1

The easiest way, if you allow them to query the entire database, is to simply add 1 = 1 to your statement. Sort of

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

Answer 2

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

 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 3

 SELECT col1, col2 FROM tblThreads WHERE case when @keywords is not null then description like '%' + @keywords + '%' when @price is not null then price like'%' + @price + '%' end