CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
Karthikeyan
Karthikeyan

Reputation: 143

Conditional filtering in SQL server

I need to write a stored proc for a search page. The search page allows the user to choose "Any" for some filters and each filter maps to a column in my DB.

What is the best way to approach this in my stored proc:

(1) Should I go with dynamic SQL (sp_executesql) and construct the query based on the filters chosen? Would this have any negative impact on caching of the execution plan?

(2) Should I translate "Any" to all possible values and use the same in a static query?

(3) Should I store the results in a temporary table using the mandatory filters and then apply the optional filters (that support ANY option) one-by-one on these results?

(4) Any other approach that I haven't thought of?

Upvotes: 0

Views: 1294

Answers (1)

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174299

Without really knowing how the user chooses the filter, I would do something like this:

SELECT * FROM TABLE 
WHERE (FILTER_FOR_COL_A IS NULL OR COL_A = FILTER_FOR_COL_A) 
AND   (FILTER_FOR_COL_B IS NULL OR COL_B = FILTER_FOR_COL_B)

FILTER_FOR_COL_A is the filter value for column COL_A. If the user has choosen ANY, FILTER_FOR_COL_A will be NULL. Obviously, if COL_A can be NULL and the user should be able to specify this as a filter, NULL isn't the best way to represent the ANY filter. You would need to think of another value or a second parameter for this. Additionally, this approach won't work if the user can specify multiple filter values for one column.

Upvotes: 2

Related Questions