存储过程位参数激活附加where子句以检查是否为null

Question

我有一个存储过程，如下所示：

CREATE PROCEDURE dbo.usp_TestFilter
  @AdditionalFilter BIT = 1
AS
  SELECT *
  FROM dbo.SomeTable T
  WHERE
    T.Column1 IS NOT NULL
    AND CASE WHEN @AdditionalFilter = 1 THEN
      T.Column2 IS NOT NULL

不用说，这是行不通的。如何激活检查@AdditionalFilter参数的附加where子句？谢谢你的帮助。

Answer 1

CREATE PROCEDURE dbo.usp_TestFilter
  @AdditionalFilter BIT = 1
AS
  SELECT *
  FROM dbo.SomeTable T
  WHERE
    T.Column1 IS NOT NULL
    AND (@AdditionalFilter = 0 OR
      T.Column2 IS NOT NULL)

如果@AdditionalFilter为0，则不会计算该列，因为它不会影响括号之间部分的结果。如果它是除0以外的任何值，则将计算列条件。

Answer 2

这种做法往往会使查询优化器感到困惑。我见过SQL Server2000以完全相反的方式构建执行计划，并在设置了标志时在Column1上使用索引，反之亦然。SQL Server2005似乎至少在第一次编译时就获得了正确的执行计划，但这样就有了新的问题。系统缓存已编译的执行计划，并尝试重用它们。如果您首先以一种方式使用查询，那么即使额外的参数发生变化，它仍然会以这种方式执行查询，并且不同的索引会更合适。

通过在EXEC语句中使用WITH RECOMPILE，或每次在CREATE PROCEDURE语句上指定WITH RECOMPILE，可以在每次执行时强制重新编译存储过程。SQL Server每次重新解析和优化查询时都会受到影响。

通常，如果查询的形式要更改，请使用带有参数的动态SQL生成。SQL Server还将缓存参数化查询和自动参数化查询的执行计划(它试图推断哪些参数是参数)，甚至是常规查询，但它将大部分权重放在存储过程执行计划上，然后依次是参数化查询、自动参数化查询和常规查询。如果服务器需要内存做其他事情，那么权重越高，在计划被丢弃之前，它在RAM中停留的时间就越长。

Answer 3

CREATE PROCEDURE dbo.usp_TestFilter
  @AdditionalFilter BIT = 1
AS
  SELECT *
  FROM dbo.SomeTable T
  WHERE
    T.Column1 IS NOT NULL
    AND (NOT @AdditionalFilter OR T.Column2 IS NOT NULL)