在SQL Server中执行动态SQL命令

问题

在某些应用程序中，由于对数据库服务器发出查询的动态特性，因此采用硬编码的SQL语句并不吸引人。因此，有时需要动态地动态创建SQL语句，然后运行该命令。这可以从应用程序的角度非常简单地完成，无论您使用的是ASP.NET，ColdFusion还是任何其他编程语言，都可以即时构建SQL语句。但是，如何从SQL Server存储过程中执行此操作？

解

如何在SQL Server中构建动态SQL语句

SQL Server提供了几种运行动态构建的SQL语句的方法。以下是一些选择：

1. 用参数编写查询
2. 使用 EXEC
3. 使用 sp_executesql

 

注意事项

尽管动态生成SQL代码是动态构建语句的一种简便方法，但确实存在一些缺点。 

一个问题是 SQL注入的潜在可能性， 其中恶意代码被插入到正在构建的命令中。下面的示例很容易上手，但是您应该了解SQL注入以及通过确保代码在执行正在生成的语句之前可以检查任何问题的健壮性来防止它的方法。

另一个问题是动态生成代码可能会导致性能问题。您实际上并不知道用户如何使用代码，因此查询可能会执行您未曾期望的事情，从而成为性能问题。因此，请再次确保在执行生成的代码之前，您的代码检查所有潜在的问题。

通过编写带有参数的查询来实现动态SQL

如果只需要将参数传递到SQL语句的WHERE子句中，则第一种方法非常简单。假设我们需要从客户表中查找所有记录，其中City ='London'。如下面的示例所示，这很容易做到。

DECLARE @city varchar(75)

SET @city = 'London'

SELECT * FROM Person.Address WHERE City = @city

我们可以将上述内容转换为存储过程，如下所示：

CREATE PROCEDURE dbo.uspGetCustomers @city varchar(75)
AS
BEGIN
   SELECT * FROM Person.Address WHERE City = @city
END
GO

然后可以执行以下操作：

dbo.uspGetCustomers @city = 'London'

使用EXEC的动态SQL命令

使用这种方法，您可以即时构建SQL语句，并且几乎可以做任何需要构造该语句的事情。假设我们希望能够将列列表与城市一起传递。

在此示例中，我们要获取AddressID，AddressLine1和City列，其中City ='London'。

从该示例中可以看到，处理@city值并非一帆风顺，因为您还需要定义额外的引号才能将字符值传递到查询中。这些额外的引号也可以在语句中完成，但是无论哪种方式，您都需要指定额外的单引号，以便正确构建查询并因此运行。

DECLARE @sqlCommand varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)

SET @columnList = 'AddressID, AddressLine1, City'
SET @city = '''London'''
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM Person.Address WHERE City = ' + @city

EXEC (@sqlCommand)

使用sp_executesql的动态SQL命令

使用这种方法，您仍然可以动态地构建查询，但是您也可以像示例1中那样使用参数。这省去了必须处理多余的引号才能正确构建查询的需要。此外，使用这种方法可以确保传递到查询中的数据值是正确的数据类型。

DECLARE @sqlCommand nvarchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)

SET @columnList = 'AddressID, AddressLine1, City'
SET @city = 'London'
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM Person.Address WHERE City = @city'

EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city

因此，这是编写动态查询的三种不同方式。除了上述内容以外，这里还有一些其他文章，它们为您提供了有关设置和使用动态SQL的其他观点。