Back

Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
in SQL by (6.1k points)

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

1 Answer

0 votes
by (12.7k points)
edited by

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.

Related questions

0 votes
1 answer
0 votes
1 answer
asked Jan 17, 2020 in SQL by anmolj (9k points)
0 votes
1 answer

Browse Categories

...