TSQL----构建WHERE子句

概述

许多时候我们需要在查询或存储过程中使用条件WHERE子句来构建查询，因为可以通过用户指定的值来过滤查询．

使用条件WHERE子句构建的查询有许多方法，大多数人采用的一种方法就是使用动态查询字符串，而也有一些人并不赞同这一观点，由于动态查询在执行前并不能很好地重用缓存执行计划．本文主要介绍构建条件WHERE子句的不同方法．

建立／执行动态查询

首先我们来看一个简单的例子，该例子创建基于参数的动态查询

注意：

本文所介绍的示例均采用SQL Server 2005的示例数据库AdevetureWorks．

脚本一：

/*

EXECUTE ProductSearch1 NULL

EXECUTE ProductSearch1 'AR'

*/

CREATE PROCEDURE ProductSearch1

(

@ProductNumber VARCHAR(20)

)

AS

SET NOCOUNT ON

DECLARE @sql VARCHAR(MAX)

SET @sql = ' SELECT * FROM Production.Product '

IF @ProductNumber IS NOT NULL BEGIN

SET @sql = @sql + ' WHERE ProductNumber LIKE ''' +

@ProductNumber + '%'''

END

-- 输出＠sql用于调试

-- PRINT @sql

EXEC(@sql)

上述方法的一个问题是当对查询的类型更改时会了编译时错误，一些修改可能导致构造查询不正确，语法错误或列名不正确．

对于上述的查询，若要使用多个参数就显得不足，最佳的方法是添加WHERE子句．

脚本二：

/*

EXECUTE ProductSearch2 NULL, NULL, NULL

EXECUTE ProductSearch2 'CA', NULL, NULL

EXECUTE ProductSearch2 NULL, 'Black', NULL

EXECUTE ProductSearch2 NULL, NULL, 375

EXECUTE ProductSearch2 'CA', 'Black', NULL

EXECUTE ProductSearch2 'CA', 'Black', 375

*/

CREATE PROCEDURE ProductSearch2

(

@ProductNumber VARCHAR(20),

@Color VARCHAR(10),

@ReOrderPoint INT

)

AS

SET NOCOUNT ON

DECLARE @sql VARCHAR(MAX)

SET @sql = ' SELECT * FROM Production.Product '

-- 至少有一个参数且不为空就添加WHERE

IF @ProductNumber IS NOT NULL

OR @Color IS NOT NULL

OR @ReOrderPoint IS NOT NULL

BEGIN

SET @sql = @sql + ' WHERE '

END

IF @ProductNumber IS NOT NULL BEGIN

SET @sql = @sql + ' ProductNumber LIKE '''

+ @ProductNumber + '%'''

END

-- 判断是否添加’AND’运算符
IF @Color IS NOT NULL BEGIN

IF @ProductNumber IS NOT NULL

SET @sql = @sql + ' AND '

SET @sql = @sql + ' Color LIKE '''

+ @Color + '%'''

END

IF @ReOrderPoint IS NOT NULL BEGIN

IF @ProductNumber IS NOT NULL OR @Color IS NOT NULL

SET @sql = @sql + ' AND '

SET @sql = @sql + ' ReorderPoint = ' +

CAST(@ReOrderPoint AS VARCHAR)

END

-- 输出＠sql用于调试

-- PRINT @sql

EXEC(@sql)

细心分析该代码，略显复杂．首先，代码判断是否要包含WHERE子句，接着判断是否采用’AND’运算符，注意在运算符的前面应该添加一个空格，例如：有时候我们可能写这样的SET @sql = @sql + ‘AND’，用SET @sql = @sql + ‘ AND ’代替．这显然是很好的习惯．

如果有许多参数，可以添加诸如上面的代码，这无疑是越来越复杂，脚本三是本查询的另一个版本．

脚本三：

/*

EXECUTE ProductSearch3 NULL, NULL, NULL

EXECUTE ProductSearch3 'CA', NULL, NULL

EXECUTE ProductSearch3 NULL, 'Black', NULL

EXECUTE ProductSearch3 NULL, NULL, 375

EXECUTE ProductSearch3 'CA', 'Black', NULL

EXECUTE ProductSearch3 'CA', 'Black', 375

*/

CREATE PROCEDURE ProductSearch3

(

@ProductNumber VARCHAR(20),

@Color VARCHAR(10),

@ReOrderPoint INT

)

AS

SET NOCOUNT ON

DECLARE @sql VARCHAR(MAX)

DECLARE @where VARCHAR(MAX)

-- 构建WHERE子句
SET @where = ''

IF @ProductNumber IS NOT NULL BEGIN

SET @where = ' ProductNumber LIKE '''

+ @ProductNumber + '%'''

END

IF @Color IS NOT NULL BEGIN

SET @where = @where +

CASE WHEN LEN(@where) > 0 THEN ' AND ' ELSE ' ' END +

' Color LIKE ''' + @Color + '%'''

END

IF @ReOrderPoint IS NOT NULL BEGIN

SET @where = @where +

CASE WHEN LEN(@where) > 0 THEN ' AND ' ELSE ' ' END +

' ReorderPoint = ' + CAST(@ReOrderPoint AS VARCHAR)

END

SET @sql = ' SELECT * FROM Production.Product '

IF LEN(@where) > 0 BEGIN

SET @sql = @sql + ' WHERE ' + @where

END

-- 输出＠sql用于调试

-- PRINT @sql

EXEC(@sql)

上述代码略有一点灵活，其最大方便就是对多参数的检测，通过对每一个参数进行IF条件的判断来构建查询条件．

WHERE子句是有选择地添加，只需判断@where变量是否为空，这里需要考虑在添加”AND”前，检测字符串是否为空，若不为空，则添加”AND”．

此版本也有另外一个版本，使用＂WHERE 1=1＂来构建，详细见脚本四．

脚本四：

/*

EXECUTE ProductSearch4 NULL, NULL, NULL

EXECUTE ProductSearch4 'CA', NULL, NULL

EXECUTE ProductSearch4 NULL, 'Black', NULL

EXECUTE ProductSearch4 NULL, NULL, 375

EXECUTE ProductSearch4 'CA', 'Black', NULL

EXECUTE ProductSearch4 'CA', 'Black', 375

*/

CREATE PROCEDURE ProductSearch4

(

@ProductNumber VARCHAR(20),

@Color VARCHAR(10),

@ReOrderPoint INT

)

AS

SET NOCOUNT ON

DECLARE @sql VARCHAR(MAX)

DECLARE @where VARCHAR(MAX)

-- 构建WHERE子句

SET @where = ''

IF @ProductNumber IS NOT NULL BEGIN

-- 直接添加”AND”

SET @where = ' AND ProductNumber LIKE '''

+ @ProductNumber + '%'''

END

IF @Color IS NOT NULL BEGIN

-- 直接添加"AND"

SET @where = @where +' AND Color LIKE '''

+ @Color + '%'''

END

IF @ReOrderPoint IS NOT NULL BEGIN

-- 直接添加"AND"

SET @where = @where + ' AND ReorderPoint = '

+ CAST(@ReOrderPoint AS VARCHAR)

END

SET @sql = '

SELECT *

FROM Production.Product

WHERE 1=1 ' + @where

-- 输出＠sql用于调试

-- PRINT @sql

EXEC(@sql)

查看上述代码，有一点不同的是，代码开始并不检测筛选变量的长度而直接添加＂AND＂,其次WHERE子句总是使用，在添加AND或WHERE之前无需检测字符串的长度．

到目前为止，介绍的几种方法都能很好地工作，但也有些时候会出现问题．

SQL注入

上述提到的一些方法有一个问题，就是SQL注入．我们来看一个例子来说明问题．

如果执行刚才创建的过程：

EXECUTE ProductSearch4 'CA', 'Black', 375

当执行上述过程，产生了如下的SQL代码：

SELECT * FROM Production.Product

WHERE 1=1 AND ProductNumber LIKE 'CA%' AND Color LIKE 'Black%'

AND ReorderPoint = 375

如果执行以下参数的同样查询会有什么情况发生呢？

EXECUTE ProductSearch4 'C'';DELETE Product --',NULL, NULL

当该参数通过时，存储过程将执行以下代码：

SELECT *

FROM Production.Product

WHERE 1=1 AND ProductNumber LIKE 'C';DELETE Product --%'

当运行该查询时，将删除Products表中的记录，这就是SQL注入的简单例子．

使用sp_executesql

通过使用sp_executesql代替使用EXEC()来执行动态查询功能，可以避免SQL注入的问题，使用sp_executesql的另一个优势就是执行查询可以得到重用．脚本五是重写上述的查询．

脚本五：

--EXECUTE ProductSearch5 'CA%', 'Black', 375

CREATE PROCEDURE ProductSearch5

(

@ProductNumber VARCHAR(20),

@Color VARCHAR(10),

@ReOrderPoint INT

)

AS

SET NOCOUNT ON

DECLARE @sql NVARCHAR(MAX)

DECLARE @params NVARCHAR(MAX)

-- 构建基本查询
SELECT @sql = '

SELECT *

FROM Production.Product

WHERE 1=1 '

-- 构建参数列表
SELECT @params = '

@ProductNumber VARCHAR(20),

@Color VARCHAR(10),

@ReOrderPoint INT'

IF @ProductNumber IS NOT NULL

SELECT @sql = @sql + ' AND ProductNumber LIKE @ProductNumber '

IF @Color IS NOT NULL

SELECT @sql = @sql + ' AND Color LIKE @Color '

IF @ReOrderPoint IS NOT NULL

SELECT @sql = @sql + ' AND ReorderPoint = @ReOrderPoint '

-- 执行查询
EXEC sp_executesql @sql, @params, @ProductNumber, @Color, @ReorderPoint

不用动态查询来构造WHERE子句

基于需要的运算符的比较，也有其他方法可用，下面的示例将介绍如何使用LIKE运算符来构建WHERE子句．

脚本五（２）：

/*

EXECUTE ProductSearch5 NULL, NULL

EXECUTE ProductSearch5 'CA%', NULL

EXECUTE ProductSearch5 NULL, 'Black%'

EXECUTE ProductSearch5 'CA%', 'Black%'

*/

CREATE PROCEDURE ProductSearch5

(

@ProductNumber VARCHAR(20),

@Color VARCHAR(10)

)

AS

SET NOCOUNT ON

SELECT *

FROM Production.Product

WHERE ProductNumber LIKE ISNULL(@ProductNumber, '%')

AND Color LIKE ISNULL(@Color, '%')

上述代码与脚本五比较，是最简单的，这里使用了ISNULL函数．如果参数为空，则使用通配符%来进行匹配，不为空则使用实际的参数进行匹配．当然也有其他的一些方法，使用CASE语句来完成．

脚本六：

/*

EXECUTE ProductSearch6 NULL, NULL

EXECUTE ProductSearch6 'CA%', NULL

EXECUTE ProductSearch6 NULL, 'Black%'

EXECUTE ProductSearch6 'CA%', 'Black%'

*/

CREATE PROCEDURE ProductSearch6

(

@ProductNumber VARCHAR(20),

@Color VARCHAR(10)

)

AS

SET NOCOUNT ON

SELECT *

FROM Production.Product

WHERE

ProductNumber LIKE CASE

WHEN @ProductNumber IS NULL THEN '%'

ELSE @ProductNumber END

AND Color LIKE CASE

WHEN @Color IS NULL THEN '%'

ELSE @Color END

使用OR运算符来实现也有一例：

脚本七：

/*

EXECUTE ProductSearch7 NULL, NULL

EXECUTE ProductSearch7 'CA%', NULL

EXECUTE ProductSearch7 NULL, 'Black%'

EXECUTE ProductSearch7 'CA%', 'Black%'

*/

CREATE PROCEDURE ProductSearch7

(

@ProductNumber VARCHAR(20),

@Color VARCHAR(10)

)

AS

SET NOCOUNT ON

SELECT *

FROM Production.Product

WHERE

(@ProductNumber IS NULL OR ProductNumber LIKE @ProductNumber)

AND

(@Color IS NULL OR Color LIKE @Color)

对于大多数比较运算符，CASE和OR可以正常工作（LIKE,=,>=,<=），然而对于ISNULL(),>,脚本八：

/*

EXECUTE ProductSearch8 NULL

EXECUTE ProductSearch8 375

*/

CREATE PROCEDURE ProductSearch8

(

@ReorderPoint INT

)

AS

SET NOCOUNT ON

/*

Option 1

*/

SELECT *

FROM Production.Product

WHERE ReorderPoint = ISNULL(@ReorderPoint,ReorderPoint)

/*

Option 2

*/

SELECT *

FROM Production.Product

WHERE ReorderPoint =

CASE

WHEN @ReorderPoint IS NULL THEN ReorderPoint

ELSE @ReorderPoint

END

/*

Option 3

*/

SELECT *

FROM Production.Product

WHERE @ReorderPoint IS NULL OR ReorderPoint = @ReorderPoint

对于包含,<>这样的运算符，下面脚本仅有第三个选项能正常工作．

脚本九：

/*

EXECUTE ProductSearch9 NULL

EXECUTE ProductSearch9 375

*/

CREATE PROCEDURE ProductSearch9

(

@ReorderPoint INT

)

AS

SET NOCOUNT ON

/*

Option 1

This will not work if "@ReorderPoint" IS NULL

*/

SELECT *

FROM Production.Product

WHERE ReorderPoint > ISNULL(@ReorderPoint,ReorderPoint)

/*

Option 2

This will not work if "@ReorderPoint" IS NULL

*/

SELECT *

FROM Production.Product

WHERE ReorderPoint >

CASE

WHEN @ReorderPoint IS NULL THEN ReorderPoint

ELSE @ReorderPoint

END

/*

Option 3

Winner!

*/

SELECT *

FROM Production.Product

WHERE @ReorderPoint IS NULL OR ReorderPoint > @ReorderPoint

从上面注释行可以知道，CASE和ISNULL可能出问题，而使用OR似乎可以避免．

注：读者朋友若要下载该文档的PDF，请点击链接下载。 

有关构造WHERE语句条件的文章可以参考以下两篇文章：

l http://www.sommarskog.se/dyn-search.html
l http://www.sommarskog.se/dynamic_sql.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/355374/viewspace-495712/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/355374/viewspace-495712/