65.9K
CodeProject 正在变化。 阅读更多。
Home

在静态 SQL 中实现动态 WHERE 子句

John-ph
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.68/5 (31投票s)

2007年11月8日

CPOL

5分钟阅读

viewsIcon

655576

downloadIcon

566

本文介绍了在静态 SQL 中实现动态 WHERE 子句的各种方法。

目录

引言

我之前的文章是关于 在存储过程中构建动态 SQL。我解释了如何使用 sp_executesql 命令编写和执行动态 SQL。那么，当我们查看这两种方法（静态和动态）的查询执行计划时；速度和性能存在巨大差异，而静态 SQL 会被检查语法错误、解析、编译，并且执行计划会存储在 SQL Server 的缓存中以供后续执行。正如我在上一篇文章中作为特别提示所提到的，变量中的动态 SQL 查询在执行之前不会被编译、解析、检查错误。SQL Server 无法重用动态 SQL 语句生成的执行计划。当性能是首要考虑因素时，就应该避免使用动态 SQL 语句。速度、性能和可重用性是动态 SQL 已知的问题。在这里，我想展示几种在静态 SQL 中实现动态 WHERE 子句的方法。我很高兴写这篇文章，它会迫使您思考更好的替代方案，而不是直接编写动态 SQL。对于阅读本文的人，我建议您看一下我之前的文章以获得更好的理解。了解这两种方法的详细信息始终是一个优势，这将帮助您选择更有效的方法。

实现动态 WHERE 子句

使用 COALESCE

SQL Server 中的 COALESCE 函数在处理包含 NULL 值的列时用途更广。它帮助我们采用更有效的方法来构建动态 WHERE 子句。在我们举例之前，让我解释一下这个“COALESCE 函数”是如何工作的。

基本语法：COALESCE()

COALESCE ( expression1 , expression2, expression3....n)

COALESCE 函数接受多个表达式作为参数，并从左到右处理表达式列表。该函数返回表达式列表中的第一个非 NULL 表达式。如果所有参数都为 NULL，则返回 NULL。

注意：所有表达式必须是同一类型，或者必须隐式转换为同一类型。

让我们举一个例子 - 具有常见字段的 Employee 表，并将语句包装在存储过程中。以下 Transact-SQL CREATE TABLE 语句用于在您的数据库中创建 Employees 表。

/* Transact-Sql to create the table tblEmployees */
CREATE TABLE tblEmployees
(
    EmployeeID       SMALLINT IDENTITY(1001,1) NOT NULL,
    EmployeeName     NVARCHAR(100) NOT NULL,
    Department       NVARCHAR(50) NOT NULL,
    Designation      NVARCHAR(50) NOT NULL,
    JoiningDate      DATETIME NOT NULL,
    Salary           DECIMAL(10,2) NOT NULL
)

以下 INSERT 语句会将一些示例记录插入到 tblEmployee 表中。

/* Transact SQL to insert some sample records into tblEmployees table */
INSERT INTO tblEmployees
(EmployeeName, Department, Designation,  JoiningDate, Salary) VALUES
('John Smith', 'IT Research', 'Research Analyst', '02/08/2005', 23000.00)

INSERT INTO 
tblEmployees(EmployeeName, Department, Designation, JoiningDate, Salary) 
VALUES('John Micheal', 'IT Operations', 'Manager', '07/15/2007', 15000.00)

INSERT INTO 
tblEmployees(EmployeeName, Department, Designation, JoiningDate, Salary) 
VALUES('Will Smith', 'IT Support', 'Manager', '05/20/2006', 13000.00)

下面的存储过程（示例 2.1）可用于在 Employee（tblEmployees）表中搜索以下详细信息：

  • 按 Name 搜索特定的 Employee 详细信息。
  • 列出特定 Department 中的 Employees。
  • 列出特定 Designation 中的 Employees。
  • 列出去年加入公司的 Employees。
  • 列出 Salary >= 某个特定金额的 Employees。
  • 以上任何一种条件或所有这些条件。

存储过程中的 SELECT 查询使用 COALESCE 函数动态实现 WHERE 子句以获得所需结果。

示例 2.1 - 使用 COALESCE

/* Create Stored Procedure 'sp_EmployeeSelect_Coalesce'. 
Example 2.1 - Using Coalesce */ 

Create Procedure sp_EmployeeSelect_Coalesce
    @EmployeeName NVarchar(100),
    @Department NVarchar(50),
    @Designation NVarchar(50),
    @StartDate DateTime,
    @EndDate DateTime,
    @Salary Decimal(10,2)
        
AS
      Set NoCount ON
 
    Select * From tblEmployees 
    where EmployeeName = Coalesce(@EmployeeName, EmployeeName) AND
        Department = Coalesce(@Department, Department ) AND
        Designation = Coalesce(@Designation, Designation) AND
        JoiningDate >= Coalesce(@StartDate, JoiningDate) AND 
        JoiningDate <= Coalesce(@EndDate, JoiningDate) AND
        Salary >= Coalesce(@Salary, Salary)

    If @@ERROR <> 0 GoTo ErrorHandler
    Set NoCount OFF
    Return(0)
  
ErrorHandler:
    Return(@@ERROR)
GO

在上面的存储过程中，对于 WHERE 子句中的每个条件，COALESCE 函数会选择第一个非空值并将其用于比较操作。如果输入参数值为 NULL，则 coalesce 函数返回等于其自身的值。这会导致该操作返回相应的行。

使用 ISNULL

ISNULL 是一个 T-SQL 系统函数，用于处理 NULL 值，它接受两个参数，第一个参数用于检查表达式，第二个参数是当检查表达式值为 NULL 时的替换值。我们可以说 ISNULL 等同于带有两个参数的 COALESCE 函数。

基本语法：ISNULL()

ISNULL ( check_expression , replacement_value )

注意：replacement_value 必须与 check_expresssion 具有相同的类型。

让我们以上面的例子为例，编写使用 ISNULL 函数动态构建 WHERE 子句的存储过程。

示例 2.2 - 使用 ISNULL

/* Create Stored Procedure 'sp_EmployeeSelect_ISNULL'. 
Example 2.2 - Using IsNull */ 

Create Procedure sp_EmployeeSelect_ISNULL
    @EmployeeName NVarchar(100),
    @Department NVarchar(50),
    @Designation NVarchar(50),
    @StartDate DateTime,
    @EndDate DateTime,
    @Salary Decimal(10,2)
AS
      Set NoCount ON
  
    Select * From tblEmployees 
    where EmployeeName = IsNull(@EmployeeName, EmployeeName) AND
        Department = IsNull(@Department, Department ) AND
        Designation = IsNull(@Designation, Designation) AND
        JoiningDate >= IsNull(@StartDate, JoiningDate) AND 
        JoiningDate <= IsNull(@EndDate, JoiningDate) AND
        Salary >= IsNull(@Salary, Salary)

    If @@ERROR <> 0 GoTo ErrorHandler
    Set NoCount OFF
    Return(0)
  
ErrorHandler:
    Return(@@ERROR)
GO

您可以在示例 2.2 中看到 - WHERE 子句是使用 ISNULL 函数动态构建的。它评估表达式并检查参数值是否为 NULL。当此检查表达式返回非 NULL 值时，它会在比较操作中使用参数值。当检查表达式返回 null 时，它会使用等于自身的值，这会导致该操作返回所有行。

使用 CASE

CASE 函数在 SQL Server 中等同于 COALESCE 函数。它评估一组条件并从多个可能的返回表达式中返回一个结果表达式。CASE 函数有两种类型：

  • 简单 CASE
  • 搜索式 CASE

基本语法：简单 CASE

CASE input_expression
   WHEN (when_expression1 IS NOT NULL) THEN result_expression1
   WHEN (when_expression2 IS NOT NULL) THEN result_expression2
   ...
   WHEN (when_expressionN IS NOT NULL) THEN result_expressionN
   ELSE else_result_expression
END

基本语法：搜索式 CASE

CASE
   WHEN (boolean_expression1 IS NOT NULL) THEN result_expression1
   WHEN (boolean_expression2 IS NOT NULL) THEN result_expression2
   ...
   WHEN (boolean_expressionN IS NOT NULL) THEN result_expressionN
   ELSE else_result_expression
END

简单 CASE 函数将 input_expression 与 when_expression 进行比较以获得所需的 result_expression。搜索式 CASE 函数评估一组布尔表达式以获得所需的 result_expression。让我们以相同的示例为例，编写使用 CASE 函数动态构建 WHERE 子句的存储过程。

示例 2.3 - 使用 CASE

/* Create Stored Procedure 'sp_EmployeeSelect_Case'. 
Example 2.3 - Using Case-When */ 

Create Procedure sp_EmployeeSelect_Case
    @EmployeeName NVarchar(100),
    @Department NVarchar(50),
    @Designation NVarchar(50),
    @StartDate DateTime,
    @EndDate DateTime,
    @Salary Decimal(10,2)
AS
      Set NoCount ON
  
    Select * From tblEmployees where EmployeeName = 
    Case When @EmployeeName Is Not Null Then @EmployeeName 
    Else EmployeeName End AND Department = 
    Case When @Department Is Not Null Then @Department 
    Else Department End AND Designation = 
    Case When @Designation Is Not Null Then @Designation 
    Else Designation End AND JoiningDate >= 
    Case When @StartDate Is Not Null Then @StartDate 
    Else JoiningDate End AND JoiningDate <= 
    Case When @EndDate Is Not Null Then @EndDate 
    Else JoiningDate End AND Salary >= 
    Case When @Salary Is Not Null Then @Salary 
    Else Salary End 

    If @@ERROR <> 0 GoTo ErrorHandler
    Set NoCount OFF
    Return(0)
  
ErrorHandler:
    Return(@@ERROR)
GO

您可以在示例 2.3 中看到，WHERE 子句是使用 CASE 函数动态构建的。它评估表达式并检查参数值是否为 NULL。当此布尔表达式返回 true 时，它会在比较操作中使用参数值。当布尔表达式返回 false 时，它会使用等于自身的值，这会导致该操作返回所有行。

替代方案

这是 CodeProject 成员在我第一篇文章的讨论中提出的一种替代方法。这种替代方法既不使用 COALESCE 也不使用 CASE 函数来构建动态 WHERE 子句，而是使用了等效的逻辑 - 值得使用。

示例 2.4 - 替代方法

/* Create Stored Procedure 'sp_EmployeeSelect_Alternate'. 
Example 2.4 - Alternate */ 

Create Procedure sp_EmployeeSelect_Alternate
    @EmployeeName NVarchar(100),
    @Department NVarchar(50),
    @Designation NVarchar(50),
    @StartDate DateTime,
    @EndDate DateTime,
    @Salary Decimal(10,2)
AS 
    Set NoCount ON
  
    SELECT * FROM tblEmployees
    WHERE (@EmployeeName Is Null OR @EmployeeName = EmployeeName) AND
        (@Department Is Null OR @Department = Department) AND
        (@Designation Is Null OR @Designation = Designation) AND
        (@Salary Is Null OR @Salary = Salary) AND
        (@StartDate Is Null OR @EndDate Is Null OR 
        (@StartDate Is Not Null AND @EndDate Is Not Null AND 
        JoiningDate BETWEEN @StartDate AND @EndDate))

    If @@ERROR <> 0 GoTo ErrorHandler
    Set NoCount OFF
    Return(0)
  
ErrorHandler:
    Return(@@ERROR)
GO

结论

希望您已经学会了如何在静态 SQL 中实现动态 WHERE 子句。在所有示例中，我都展示了如何处理输入参数为 NULL 的情况。即使是用于多个比较的不同非 NULL 值，您仍然可以更有效地使用简单的 CASE 和搜索式 CASE 函数进行处理。因此，我得出结论，静态 SQL 更快、更安全，在大多数情况下，我们不需要使用动态 SQL 语句。

参考

历史

  • 2007 年 11 月 8 日：初始发布
© . All rights reserved.