许多用于缩小范围的可选筛选器在 SQL Server 中产生结果

本文关键字：Server SQL 结果 筛选 缩小 用于 范围 许多 | 更新日期: 2023-09-27 18:31:57

我有一个包含这些列的汽车表：

ID
BrandID
ModelID
ColorID
ProductionYear
Price
IsSecondHand
.
.
.

品牌，型号，颜色和...都有自己的表格。

用户有广泛的过滤选项，换句话说，所有的过滤都是可选的，用户可以选择也可能不会选择品牌、型号、颜色等，可以选择生产年份范围、价格范围。 甚至有些过滤器具有多个值，例如用户可以选择查看红色或白色的汽车。 或者可以选择过滤多个模型。

我想编写

一个存储过程来返回结果，我想找到一种方法来编写具有良好性能的更简单的代码。我不想在我的过程中使用 1000 if 子句。

有什么想法吗？

许多用于缩小范围的可选筛选器在 SQL Server 中产生结果

如果查询可以依赖于许多不同的列，则没有好的单个 T-SQL 语句。要获得像样的执行计划这样的任何东西，您需要针对所需确切组合的 T-SQL。执行此操作的方法是动态构建 SQL。

现在您有 3 个选项：

  • 在存储过程中生成 T-SQL，并使用 sp_ExecuteSQL 执行它（保留正确的参数化和查询计划重用）
  • 首先不要使用存储过程：在 C# 中生成 T-SQL 并执行它
  • 使用ORM

第一个选项是可行的，但很痛苦 - T-SQL根本不是真的用于此。但你可以做到。例如（缩写）：

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

C# 选项要容易得多 IMO - 通常通过 StringBuilder - 只需添加所需的子句和参数即可。

最后，使用 ORM（在本例中为 LINQ 提供程序）：

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);
// ...

尝试创建如下所示的存储过程

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

您可以从代码隐藏生成 SQL。所以你只能放必要的条件

更新

如果用户可以为一个参数选择多个值，我建议您使用参数化 SQL IN 子句或表值参数

相关文章：