Many optional filters for narrowing results in SQL Server

Question

I have a table for cars with these columns:

ID
BrandID
ModelID
ColorID
ProductionYear
Price
IsSecondHand
.
.
.

Brands, Models, Colors and... have their own tables.

Users have wide filtering options, in the other words all filter are optional, and user may or may not choose a brand, model, color, etc. can choose a productionyear range, price range. and even some filters have more than one value, for example user may choose to see cars that are red or white. or may choose to filter several models.

I want to write a stored procedure to return the results and I want to find a way to write an easier code with good performance. I don't want to use 1000 if clauses in my procedure.

Any ideas?

The only way I can see this working, without lots of conditionals, is to use dynamic SQL. — James, Sep 18 '13 at 09:49
you mean to make a dynamic query and then run it? should I make the query in sql server or in C#? — Ashkan Mobayen Khiabani, Sep 18 '13 at 09:50
Why did you mention C# in the tags list ? Are you or are you not forced to do whatever you need to do in a stored procedure ? — Eduard Dumitru, Sep 18 '13 at 09:51
If possible I would suggest you do the dynamic query in LINQ to Entities or LINQ to SQL — Eduard Dumitru, Sep 18 '13 at 09:52
@AshkanMobayenKhiabani yes, it's the only way to avoid loads of conditionals (that I can think of) that would allow you to have it as an SP. Alternatively you just do it all client-side. — James, Sep 18 '13 at 09:53
@EduardDumitru as I'm developing both sql-server and asp.net part. I can do anything in C# part or the sql part. but I want my stored procedure be independent, I don't want to need generating queries in many pages of the website. — Ashkan Mobayen Khiabani, Sep 18 '13 at 09:56
@AshkanMobayenKhiabani the "generating queries in many pages of the website" issue is avoided simply by writing your data access code in classes dedicated to data access. Your UI code calls that data access code: no duplication — Marc Gravell, Sep 18 '13 at 11:41

score 3 · Answer 1 · edited Oct 04 '19 at 17:16

If the query can depend on lots of different columns, there is no good single T-SQL statement for it. To get anything like a decent execution plan, you would want T-SQL that targets the exact combination you want. The way to do this is to build the SQL dynamically.

Now you have 3 options:

generate the T-SQL inside the stored procedure, and use sp_ExecuteSQL to execute it (retaining correct parameterization and query-plan re-use)
don't use a stored procedure in the first place: generate the T-SQL in C# and execute it
use an ORM

The first option is doable, but painful - T-SQL simply isn't really meant for this. But you can do it. For example (abbreviated):

declare @tsql nvarchar(4000) = 'select * from foo ...'
if @name is not null
    set @tsql = @sql + ' and Name = @name'
if @region is not null
    set @tsql = @sql + ' and Region = @region'
-- ...
exec sp_executesql @tsql,
        N'@name nvarchar(50), @region int`,
        @name, @region

The C# option is much easier IMO - usually via StringBuilder - just add the clauses and parameters you need.

Finally, with an ORM (in this case, a LINQ provider):

IQueryable<Foo> foos = ctx.Foos;
if(name != null) foos = foos.Where(x => x.Name == name);
if(region != null) foos = foos.Where(x => x.Region == region);
// ...

Totally agree. And AshkanMobayenKhiaban, if you take what @MarcGravell is saying about the ORM part, that won't mean that you'll "need generating queries in many pages of the website". You can have yourself a nice tier for Data Access which is somehow separated from the rest of the app, maybe it's even reusable amongst many apps. — Eduard Dumitru, Sep 18 '13 at 10:00

score 2 · Accepted Answer · edited Oct 13 '19 at 07:49

2

Try creating stored procedure like below

    @BrandID INT,
    @ModelID INT
AS
    SELECT columns...
    FROM TABLE TB
    WHERE (@BrandID = 0 OR (TB.BrandID = @BrandID ))
      AND (@ModelID = 0 OR (TB.ModelID = @ModelID ))

Either you can generate the SQL from your code behind. So you can put only the necessary conditions

Update

If the user can select multiple value for one parameter, I suggest you to use Parameterizing a SQL IN clause or Table-Valued Parameters

edited Oct 13 '19 at 07:49

marc_s

732,580
175
1,330
1,459

answered Sep 18 '13 at 09:51

huMpty duMpty

14,346
14
60
99

what should I do for filters like color that user can specify more than one color – Ashkan Mobayen Khiabani Sep 18 '13 at 09:53
you should do whatever you normally would do, and maybe add an extra parameter to the stored procedure called HaveAtLeastOneColor or deduce that value of truth from the list of colors... Anyway, in the end, try to make the same logical compound @huMpty duMpty showed you in his example using that value – Eduard Dumitru Sep 18 '13 at 09:55
That does not scale well to lots of tests - you usually get pretty terrible query plans – Marc Gravell Sep 18 '13 at 09:55

Many optional filters for narrowing results in SQL Server

2 Answers2