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

在存储过程中构建动态 SQL

John-ph
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.50/5 (66投票s)

2007年10月9日

CPOL

6分钟阅读

viewsIcon

872674

downloadIcon

2717

本文解释了如何在存储过程中构建和执行动态 SQL。

引言

存储过程中的动态 SQL 是一个单个的 Transact-SQL 语句或一组存储在变量中并使用 SQL 命令执行的语句。在 SQL Server 中可能存在几种实现此目的的方法。本文将向您展示一种很好的方法。在详细解释之前，让我先说明“何时使用动态 SQL？”我们不能明确地说静态 SQL 将满足我们所有的编程需求。当我们需要根据不同的搜索参数检索一组记录时，就需要动态 SQL。例如——一个员工搜索屏幕或一个需要根据不同的 WHERE 子句执行不同 SELECT 语句的通用报告。

注意：最重要的是，变量中的动态 SQL 查询在执行之前不会被编译、解析、检查错误。

sp_executesql 与 EXECUTE 命令

动态构建的 Transact-SQL 语句可以使用 EXECUTE 命令或 sp_executesql 语句执行。在本文的示例中，我将使用 sp_executesql，它效率更高，执行速度更快，并且还支持参数替换。如果使用 EXECUTE 命令执行 SQL 字符串，那么所有参数都应该转换为字符并在执行前作为查询的一部分。但是 sp_executesql 语句提供了一种更好的实现方式。它允许我们为 SQL 字符串中指定的任何参数替换参数值。在进入实际示例之前，让我通过一个简单的示例来区分这两个命令。例如——使用 ID 在 WHERE 子句中从员工表中选择一条记录。

使用 EXECUTE 命令的基本语法

EXECUTE(@SQLStatement)

使用 sp_executesql 的基本语法

sp_executesql [@SQLStatement],[@ParameterDefinitionList],
[@ParameterValueList]

示例 1.0

/* Using EXECUTE Command */
/* Build and Execute a Transact-SQL String with a single parameter 
 value Using EXECUTE Command */

/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
/* set the parameter value */
SET @EmpID = 1001
/* Build Transact-SQL String with parameter value */
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = ' + 
CAST(@EmpID AS NVARCHAR(10))
/* Execute Transact-SQL String */
EXECUTE(@SQLQuery)

在上面的示例 1.0 中，声明了两个变量。第一个变量 @EmpID 用作 SQL 查询的参数，第二个变量 @SQLQuery 用来构建 SQL 字符串。您可以清楚地看到变量 @EmpID 被转换为 NVarchar 类型并作为 SQL 字符串的一部分。如果您打印 @SQLQuery 字符串（PRINT @SQLQuery），您将获得如下所示的实际 SQL 查询

SELECT * FROM tblEmployees WHERE EmployeeID = 1001

最后，上述查询使用 EXECUTE 命令执行。

示例 1.1

/* Using sp_executesql */
/* Build and Execute a Transact-SQL String with a single parameter 
value Using sp_executesql Command */

/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
/* set the parameter value */
SET @EmpID = 1001
/* Build Transact-SQL String by including the parameter */
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID' 
/* Specify Parameter Format */
SET @ParameterDefinition =  '@EmpID SMALLINT'
/* Execute Transact-SQL String */
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID

在示例 1.1 中，声明了两个变量。变量 @EmpID 用作 SQL 查询的参数，第二个变量 @SQLQuery 用来构建 SQL 字符串，第三个变量 @ParameterDefinition 用于在执行 SQL 字符串之前指定参数格式。如果您打印 @SQLQuery 字符串（PRINT @SQLQuery），您将获得如下所示的查询

SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID

在此示例中，您可以清楚地看到参数 @EmpID 已包含在语句中。最后，sp_executesql 获取必要的信息来执行参数替换并执行动态构建的 SQL 字符串。

  1. @SQLQuery --> 包含 SQL 语句
  2. @ParameterDefinition --> 包含参数定义
  3. @EmpID --> 包含要替换到 SQL 语句中参数的参数值。

注意：动态 SQL 字符串中包含的参数必须在参数定义列表和参数值列表中有相应的条目。

存储过程中的动态 SQL

本文的这一部分通过一个实际示例和示例过程解释了“如何在存储过程中构建和执行动态 SQL？”

示例 2.0

让我们来看一个简单的示例 - Employee 表，包含 EmployeeID、Name、Department、Designation、JoiningDate、Salary 和 Description 等常用字段。您可以使用以下 Transact-SQL CREATE TABLE 语句在数据库中创建 Employee 表。

/* 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,
    [Description]    NVARCHAR(1000) NULL 
)

以下 INSERT 语句向 tblEmployee 表插入一些示例记录

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

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

INSERT INTO tblEmployees
(EmployeeName, Department, Designation, 
 JoiningDate, Salary, [Description]) 
VALUES    
('Will Smith', 'IT Support', 'Manager', 
 '05/20/2006', 13000.00, 'Joined last year as IT Support Manager')

我们程序员可能会接到开发一个员工搜索屏幕或生成一个员工列表报告的任务，该屏幕或报告将搜索数据库并根据搜索条件返回结果。在这种情况下，搜索界面应该足够灵活，可以根据所有可能的条件搜索数据库。用户可能需要搜索以下详细信息

  • 按姓名搜索特定员工详细信息
  • 特定部门中的员工列表
  • 特定职位中的员工列表
  • 去年加入组织的员工列表
  • 薪水大于或等于某个特定金额的员工列表
  • 上述任何条件或所有条件

我在这里列出了几种可能的条件。也可能有很多其他可能性，这完全取决于用户需求。这里让我们采用这些列出的几个可能条件，并编写一个存储过程，该存储过程构建一个动态 SQL，以实现我们在 Employee 表中搜索详细信息的目的。以下 CREATE PROCEDURE 语句将创建一个存储过程“sp_EmployeeSelect”，其中包含构建动态 SQL 所需的输入参数和变量。

/* This stored procedure builds dynamic SQL and executes 
using sp_executesql */
Create Procedure sp_EmployeeSelect
    /* Input Parameters */
    @EmployeeName NVarchar(100),
    @Department NVarchar(50),
    @Designation NVarchar(50),
    @StartDate DateTime,
    @EndDate DateTime,
    @Salary    Decimal(10,2)
        
AS
    Set NoCount ON
    /* Variable Declaration */
    Declare @SQLQuery AS NVarchar(4000)
    Declare @ParamDefinition AS NVarchar(2000) 
    /* Build the Transact-SQL String with the input parameters */ 
    Set @SQLQuery = 'Select * From tblEmployees where (1=1) ' 
    /* check for the condition and build the WHERE clause accordingly */
    If @EmployeeName Is Not Null 
         Set @SQLQuery = @SQLQuery + ' And (EmployeeName = @EmployeeName)'

    If @Department Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (Department = @Department)' 
  
    If @Designation Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (Designation = @Designation)'
  
    If @Salary Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (Salary >= @Salary)'

    If (@StartDate Is Not Null) AND (@EndDate Is Not Null)
         Set @SQLQuery = @SQLQuery + ' And (JoiningDate 
         BETWEEN @StartDate AND @EndDate)'
    /* Specify Parameter Format for all input parameters included 
     in the stmt */
    Set @ParamDefinition =      ' @EmployeeName NVarchar(100),
                @Department NVarchar(50),
                @Designation NVarchar(50),
                @StartDate DateTime,
                @EndDate DateTime,
                @Salary    Decimal(10,2)'
    /* Execute the Transact-SQL String with all parameter value's 
       Using sp_executesql Command */
    Execute sp_Executesql     @SQLQuery, 
                @ParamDefinition, 
                @EmployeeName, 
                @Department, 
                @Designation, 
                @StartDate, 
                @EndDate,
                @Salary
                
    If @@ERROR <> 0 GoTo ErrorHandler
    Set NoCount OFF
    Return(0)
  
ErrorHandler:
    Return(@@ERROR)
GO

此示例存储过程接受几个参数作为输入，并使用两个变量进行构建和执行。@SQLQuery 用于构建动态 SQL 语句。@ParamDefinition 用于定义参数的格式。在每一步构建 SQL 字符串时，使用 IF 语句检查输入的参数是否为 Null。如果它不为 NULL，则该参数将包含在 SQL 语句中，这基本上在 SQL 语句的 WHERE 子句中添加了一个条件。您可以在过程中清楚地看到变量 @ParamDefinition 包含所有参数列表，最后 sp_Executesql 接收 SQL 查询、参数列表和参数值来执行 SELECT 语句。

让我们考虑上面列出的一些条件，看看这个存储过程是如何工作的。

  1. 按姓名搜索特定员工详细信息。
    /* 1. Search for specific Employee Detail with the Name say 'John Smith'. */
EXEC sp_EmployeeSelect 'John Smith', NULL, NULL, NULL, NULL, NULL

    执行上述语句将列出员工“John Smith”的详细信息。

  2. 特定部门中的员工列表，以及
  3. 特定职位中的员工列表。
    /* 2. List of Employees in a specific Department. AND 
3. List of Employees in a specific Designation. */
/* Say Department = 'IT Operations'  AND  Designation = 'Manager'*/
EXEC sp_EmployeeSelect NULL, 'IT Operations', 'Manager', NULL, NULL, NULL

    执行上述语句将列出 IT 运营部门的经理的详细信息。

在动态 SQL 中使用 Like 运算符、IN 运算符和 Order By

当我们构建动态 SQL 时，可能需要在某些情况下使用 LIKE 运算符、IN 运算符和 Order BY 子句。但是，与这些运算符和 Order By 子句一起使用的参数在使用 sp_executesql 时，其工作方式与“=”和“Between”运算符通常的工作方式不同。通常，sp_executesql 不会为 order by 子句进行参数替换，这样做会导致列引用问题。直接使用 LIKE 运算符和 IN 运算符会导致语法错误，当我们将参数包含到动态 SQL 语句中时，无法纠正此问题。此问题可以通过将实际参数值包含在动态 SQL 语句中来解决。以下示例演示了在使用 sp_executesql 时如何使用 Like 运算符、IN 运算符和 OrderBy 子句。

示例 3.0 - 使用 LIKE 运算符

示例 3.0 使用 LIKE 运算符选择名称为“John”的员工列表。在此示例中，参数未包含在 SQL 语句中，而是将参数的实际值添加到 SQL 语句中。因此，这里不需要参数定义来执行 SQL 字符串。同样适用于下面所示的其他两个示例

/* Variable Declaration */
DECLARE @EmpName AS NVARCHAR(50)
DECLARE @SQLQuery AS NVARCHAR(500)

/* Build and Execute a Transact-SQL String with a single parameter 
value Using sp_executesql Command */
SET @EmpName = 'John' 
SET @SQLQuery = 'SELECT * FROM tblEmployees 
WHERE EmployeeName LIKE '''+ '%' + @EmpName + '%' + '''' 
EXECUTE sp_executesql @SQLQuery

示例 3.1 - 使用 IN 运算符

示例 3.1 使用 IN 运算符选择员工详细信息 ( ID = 1001, 1003 )

/* Variable Declaration */
DECLARE @EmpID AS NVARCHAR(50)
DECLARE @SQLQuery AS NVARCHAR(500)

/* Build and Execute a Transact-SQL String with a single 
parameter value Using sp_executesql Command */
SET @EmpID = '1001,1003' 
SET @SQLQuery = 'SELECT * FROM tblEmployees 
WHERE EmployeeID IN(' + @EmpID + ')'
EXECUTE sp_executesql @SQLQuery

示例 3.2 - 使用 Order By 子句

示例 3.2 按“Department”列对员工记录进行排序。

/* Variable Declaration */
DECLARE @OrderBy AS NVARCHAR(50)
DECLARE @SQLQuery AS NVARCHAR(500)

/* Build and Execute a Transact-SQL String with a single parameter 
value Using sp_executesql Command */
SET @OrderBy = 'Department' 
SET @SQLQuery = 'SELECT * FROM tblEmployees Order By ' + @OrderBy

EXECUTE sp_executesql @SQLQuery

结论

在本文中，我通过几个例子解释了“如何在存储过程中构建和执行动态 SQL”。希望本文能帮助大家理解和更好地编写动态 SQL。

历史

  • 2007 年 10 月 9 日：初始发布
© . All rights reserved.