How to build a T-SQL query according to selected options

Hello!,

I have created a set of controls that will be used to build a SELECT query from the SQL Server.
If no controls (options) are selected the following query will run:
SELECT * FROM MA_CustSupp

If for example a Supplier checkbox is selected, then the following query will run:
SELECT * FROM MA_CustSupp
WHERE CustSupp = '42659'

If, for example, the Italy is selected from the list of countries and Supplier checkbox is selected, then the following query will run:
SELECT * FROM MA_CustSupp
WHERE CustSupp = '42659' AND Country LIKE '%Italia%'

So, the query will comply to selected checkboxes, options etc.

If, for example, the Italy is selected from the list of countries and Supplier checkbox is selected, and the user will put a text string "ELLER" in a textbox and click "Execute search..." button, then the folowing query should run:

SELECT * FROM MA_CustSupp
WHERE CustSuppType = '3211264' AND Country LIKE '%Italia%' AND CompanyName LIKE '%ELLER%'

There can be many various search criteries, more checkboxes, etc and that's why I would like to know which is the correct way to implement such kind of search filter.

Please, see the attached picture.
Many thanks in advance!

2.png
LVL 1
ZaurbAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
I would start with this query:
sql = " SELECT * FROM MA_CustSupp WHERE 1 = 1 ";

and for each of the filters, you add it:
sql += " AND CustSupp = '42659' ";
sql += " AND Country LIKE '%Italia%' ";

etc etc etc

this is what I found easiest in coding ...
0
 
ZaurbAuthor Commented:
many thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.