概述
许多时候我们需要在查询或存储过程中使用条件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(),>,脚本八:
/*
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/