如何创建动态 WHERE 选择而不使用 COALESCE

Question

我发现我的 SQL 2008 R2 数据库在搜索中使用 COALESCE 函数时确实遇到了困难。

代码：

where 
    i.id_categ = COALESCE(@id_categ, i.id_categ )
    and i.id_brand = COALESCE(@id_brand , i.id_brand )
    and i.id_model = COALESCE(@id_model , i.id_model )
    and i.id_type = COALESCE(@id_karoseria, i.id_type )
    and i.id_fuel = COALESCE(@id_palivo, i.id_fuel )
    and (i.year between @year_from and @year_to)
    and (i.price between @price_from and @price_to)

动态变量：

ALTER PROCEDURE [dbo].[spInzeratSelect]
     @id_categ int = null,
     @id_brand int = null,
     @id_model int = null,
     @id_fuel int = null,
     @id_type int = null,

搜索应该在有或没有这些变量的情况下工作。

基准测试：

  with COALESCE = Total Execution Time: 3582
  without COALESCE conditions = Total Execution Time: 13

你明白了区别...

是否有一个很好的解决方案如何忽略 COALESCE 并使用不同的方法创建动态 SQL 选择？

谢谢。

Answer 1

t在您非常具体的情况下，您应该将所有COALESCE 搜索参数替换为以下模式：

 AND ( (@id_brand IS NULL) OR (i.id_brand = @id_brand) )

etc

该参数在执行之前被评估为文字，因此，这样做可以使条件变得可控制。
这在功能上等同于您的查询，只不过您首先检查文字值，如果它实际上为空，则可以对其进行优化。

编辑：显然这也是@Joe Stefanelli 链接中推荐的方法。我最初是从 Erland Sommerskog 那里偷来的。

EDIT2：而且我也总是忘记提及OPTION (RECOMPILE)。