Sql where 子句在过滤器为空的情况下返回所有内容

我下面有一个 sql 表 -

SrNo  Name Value
1      A    X1
2      B    NULL
3      C    X3
4      D    X4
5      E    NULL
6      F    NULL 

我试图从表中获取所有记录，并满足以下两个条件 - a) 如果“@Value”列上的过滤器为 null，如下所示，则它应该返回包括 null 在内的所有记录

BEGIN
 Declare @Value varchar(50)
 SET @Value = NULL
 SELECT * from TestTable where Value = @Value
END

b）如果提供的@Value不为空，那么它应该返回该列（这肯定有效）

我尝试实现以下条件

BEGIN
     Declare @Value varchar(50)
     SET @Value = NULL
     SELECT * from TestTable where Value = IIF(@Value is NULL,Value,@Value)
    END

但上面也删除了空列。任何帮助或建议将不胜感激。

典型的方法是使用OR取决于是否提供了参数：

WHERE (Value = @Value OR @Value IS NULL);

但是，这可能会出现问题，因为您根据第一次调用获得了该查询的一个执行计划。这意味着如果第一次调用指定@Value = 1，这会导致索引查找，当参数为 NULL 时，第二个调用将获得索引查找（并且您不会喜欢它的性能）。类似地，在另一个方向上，第一个参数为 NULL，您将进行扫描，因为您要返回整个表，但是在第二次调用时，当您只需要一行（或很少）行时，您将仍然会进行扫描。

对此的典型反应是“添加OPTION (RECOMPILE);"!

这很棒，除非Value是唯一的，或者总是具有非常少量的任何给定值，并且您不断地使用导致小查找的显式值调用查询，您每次都无缘无故地重新编译该查询。每次需要扫描时，您每次都需要重新编译该查询，而无需这样做。

这种具有可选搜索参数的“厨房水槽”类型查询的折衷方案是使用动态 SQL（并且以不可注入的方式执行此操作很重要）：

DECLARE @sql nvarchar(max) = N'SELECT ... FROM dbo.TestTable';

IF @Value IS NOT NULL
BEGIN
  SET @sql += N' WHERE Value = @Value';
END

SET @sql += N';';

EXEC sys.sp_executesql @sql, N'@Value varchar(50)', @Value;

现在您有两个不同的查询，它们将在两种不同的场景中以可预测的方式执行，并且不需要重新编译。如果您的数据可能存在偏差（某些值Value作为扫描或不同的计划形状效果会更好），然后您可以重新编译就在这种情况下:

  SET @sql += N' WHERE Value = @Value OPTION (RECOMPILE)';

如果您有知道会导致或不会导致问题的特殊值，或者如果您有多个参数，并且其中一些参数与唯一列相反，而其他参数则不然，您甚至可以执行此操作。

更多信息：

• #BackToBasics：更新的厨房水槽示例
• 保护自己免受 SQL 注入 - 第 1 部分
• 保护自己免受 SQL 注入 - 第 2 部分