本文翻译自 Creating Dynamic Where Clauses with SQL
译者：耐心的农夫2020
时间：2020-04-12

使用用户界面数据库查询时，一种非常常见的场景是“动态搜索查询”，即在 where 子句中包含可选条件的 select 语句。尽管有很多方法可以实现这种效果，但是不同的方法复杂程度不一样。总的来说，不同方法在实现、可读性、性能和可读性方面做了取舍。本文介绍了实现这个效果的不同方法。

配置介绍

在我讨论不同方法之前，我想要先展示一下创建表和插入记录的脚本，以便说明接下来所有的概念。下面的清单创建了一张具有两个 VARCHAR 字段的表，并填充了一些数据，包括空值。

CREATE TABLE TestData
(
    ColumnA VARCHAR(20),
    ColumnB VARCHAR(25)
)

-- Insert test data
INSERT INTO TestData
    (ColumnA)
VALUES
    ('Me')

INSERT INTO TestData
    (ColumnA, ColumnB)
VALUES
    ('Me', 'You')

INSERT INTO TestData
    (ColumnA, ColumnB)
VALUES
    ('You', 'Me')

运行上面的脚本后，我们就有了下面这样一张表。

现在我们有了数据，我们可以讨论一个搜索过程的实现，该过程允许同时搜索 ColumnA 和 ColumnB，但是两个搜索都可以省略。如果未选择任何内容，则应该返回所有条目，而不仅仅是“空”条目。

动态 SQL

通常，想要构建动态 SQL 语句以满足这些需求的人第一个想法就是使用动态 SQL 进行构建。我们大多数人已经了解使用动态 SQL 相关的问题和风险。通常这种办法应该是最后一个选项，因为这样做不安全。下面是一个如何通过动态 SQL 进行操作的快速示例。

DECLARE @ColumnA VARCHAR(20)
DECLARE @ColumnB VARCHAR(25)
DECLARE @SQL NVARCHAR(1000)

SET @SQL = 'SELECT
    ColumnA,
    ColumnB
FROM TestData
WHERE 1=1'

IF(@ColumnA IS NULL)
BEGIN
    SET @SQL = @SQL + '
               AND ColumnA = ''' + @ColumnA + ''''
END

IF(@ColumnB IS NULL)
BEGIN
    SET @SQL = @SQL + ' 
                AND ColumnB = ''' + @ColumnB + ''''
END

EXEC(@SQL)

总的来说，这是可行的。但是在 @ColumnA 和 @ColumnB 数据中进行级联存在风险。一个单引号 ' 或者其他字符会破坏脚本的执行。不推荐使用这种方法查询系统。个人认为这种方法是“最后一搏”的努力。

使用 Coalesce

第二种方法 （我过去用过）使用 SQL Server 中提供的 Coalesce 函数。这个函数的作用是接受多个参数，并返回第一个非空的参数。所以如果你执行 COALESCE(@ColumnA, ColumnA) ，如果参数值为空，那就用这一列的值替换，使得该条件总是为 true。下面是使用这种方法的一个例子。

DECLARE @ColumnA VARCHAR(20)
DECLARE @ColumnB VARCHAR(25)

SELECT
    ColumnA,
    ColumnB
FROM TestData
WHERE ColumnA = COALESCE(@ColumnA, ColumnA)
    AND ColumnB = COALESCE(@ColumnB, ColumnB)

现在这是更加简洁的一种方法，而且格式相当容易阅读。但是，当对可能为空的数据进行过滤时，可能会碰到问题。我们的测试数据得出的结果集实际上类似于以下内容。

结果不包含 ColumnB 为空的第一行数据，这是因为 COALESCE 没有给该字段提供空值并且没有使条件为 true。所以这种方法在处理非空数据列的时候非常有效，但是如果有空的列，就不是一种好方法。

使用 CASE

我要提供的第三种方法解决了 COALESCE 方法的空列问题和动态 SQL 方法的安全问题。虽然这种方法最好，但是有一个缺点，可读性差。这种方法在每个列的两边使用 CASE 子句，如果参数为空，就使用值 1，从而生成了 1 = 1 子句；如果参数不为空，就构建了一个标准格式的 where 子句。下面是一个简单实现。

DECLARE @ColumnA VARCHAR(20)
DECLARE @ColumnB VARCHAR(25)

SELECT 
    ColumnA,
    ColumnB
FROM TestData
WHERE CASE WHEN @ColumnA IS NULL
            THEN 1
            ELSE ColumnA
      END = CASE WHEN @ColumnA IS NULL
                    THEN 1
                    ELSE @ColumnA
      END
    AND CASE WHEN @ColumnB IS NULL
            THEN 1
            ELSE ColumnB
      END = CASE WHEN @ColumnB IS NULL
                    THEN 1
                    ELSE @ColumnB
      END

这种方法可读性很差，但是可行的，并且不会使用动态 SQL 创建查询。与其他方法相比，查看执行计划发现不会对性能产生影响。最好的部分是，它可以显示所有记录，并且是真正动态的，并且满足我们的特定需求。

总结

我希望本文能够作为一个很好的概述，展示一些在 SQL Server 语句中创建动态 where 子句的不同方法。我确信还有其他方法也能达到同样的效果。但是本文展示的是我觉得有用的方法。请随时在下面分享您的反馈。