Re-write this Query to make it more scalable

Question

I have a page on my site which has multiple drop down boxes as filters.

So the SQL procedure for that page would be something like this

IF @Filter1 = 0, @Filter2 = 0, @Filter3 = 0
    BEGIN

          SELECT * FROM Table1        

    END

ELSE IF @Filter1 = 1, @Filter2 = 0, @Filter3 = 0
    BEGIN

          SELECT * FROM Table2        

    END

At the beginning, there were only a few results per filter so there weren't that many permutations. However, more filters have been added such that there are over 20 IF ELSE checks now.

So if each filter has 5 options, I will need to do 5*5*5 = 125 IF ELSE checks to return data dependent on the the filters.

Update The first filter alters the WHERE condition, the second filter adds more tables to the result set, the third filter alters the ORDER BY condition

How can I make this query more scalable such that I don't have to write a new bunch of IF ELSE statements to check for every condition everytime a new filter is added to the list besides using dynamic SQL...

Answer 1

You must have to have a rule table with formulaes maybe bitwise and construct a query that might plug variable data from the table and appends to a string to form the sql and the use dynamic sql to run them.

Answer 2

As much as I dislike dynamic SQL, this may be the time for it. You can build the query a little at a time, then execute it at the end.

If you're unfamiliar, the syntax is something like:

DECLARE @SQL VARCHAR(1000)
SELECT @SQL = 'SELECT * FROM ' + 'SOME_TABLE'
EXEC(@SQL)

Make sure you deal with SQL injection attacks, proper spacing, etc.

In this case, I'd do my best to put this logic in application code, but that's not always possible. If you're using LINQ-to-SQL or another LINQ framework, you should be able to do this safely, but it may take some creativity to get the LINQ query built properly.

Answer 3

You can set up a bunch of views, one for each "filter" and then select from the appropriate view based on which "filter" was selected.