To make a SQL ?

Question

There are 4 fields: Name,Status,Activities Performed And Region.We have to make search tool so that if user enter any one or two or more values from these fields it will be searched with the help of SQL query i.e. a single query should be able to take all these fields into account and check which of them are null and accordingly make dynamic execution of query.

So, the requirement is to make SQL whose WHERE CLAUSE can search on one column, combination of any two columns, combination of any three columns etc.

Please advise.

Answer 1

As long as you pass a '' value for the variables that are not entered by the user for filtering, this should do the needful, i believe

SELECT * 
FROM table 
WHERE Name LIKE '%@name%'
AND Status LIKE '%@status%'
AND [Activities Performed] LIKE '%@activities%'
AND Region LIKE '%@region%'

NOTE: This kind of query is usually non-sargable and will not perform very well under high load / heavy data circumstances.