• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

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
0
Zaurb
Asked:
Zaurb
1 Solution
 
Guy Hengel [angelIII / a3]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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now