ITPub博客

首页 > Linux操作系统 > Linux操作系统 > TSQL----构建WHERE子句

TSQL----构建WHERE子句

原创 Linux操作系统 作者:bigholy 时间:2008-11-14 22:28:09 0 删除 编辑

概述

许多时候我们需要在查询或存储过程中使用条件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子句.

脚本五(2):

/*

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(),>,<这样的运算符就会出现问题.以下存储过程描述了构建WHERE子句的”=”运算符的三种不同方法.

脚本八:

/*

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-11-08

  • 博文量
    43
  • 访问量
    84414