在做一个搜索时,有多项搜索条件,如果这么多条件单独在程序里判断是很麻烦的。今天花几个小时的时间才做好这个存储过程,用这么长别笑话我笨啊,呵呵,发现很多基础的语法都忘了,当然用SQL2008也发现了些新问题。
原理:把条件进行字符串拼接,就是判断传入的各个条件,如果传入符合条件的值了,则在拼接此条件语句,否则不拼接此条件。也就是我在页面上存在多条件查找时,如果一个文本框里不输入值了,则传入空字符串,存储过程判断如果是空字符串就不拼接此条件,此时在写代码时,省了很多判断。
详细代码如下所示,这里对代码需要注意的地方说下,也是我在做时出问题的地方:
1、如果数据库是数字类型,需要利用CONVERT(varchar(8000),@AppID)转换为字符串,如下代码中的:@strWhere=@strWhere+' and AppID='+CONVERT(varchar(8000),@AppID)
2、在数据库4个单撇号才翻译成一个单撇号,在进行字符串相等匹配时要注意前后有个单撇号,如下代码中的:@strWhere=@strWhere+' and UserIP='+''''+@UserIP+'''' 当然也可以这么写@strWhere=@strWhere+' and UserIP='''+@UserIP+'''' 原因是在字符串常量中要输出一个单撇号,需要写2个单撇号,如要输出I'm a student 需要写成select 'I''m a student'
3、字符串模糊匹配别忘'%搜索内容%'前后的单撇号 如下代码中的:@strWhere=@strWhere+' and UserName like '+''''+'%'+@UserName+'%'+'''' 为了阅读方便我把常量字符串%单独拿出来,对于常量字符串前后有单撇号括起来
4、对于ntext类型进行字符串拼接时,必须转换为varchar否则出现“ntext 和varchar 在equal to 运算符中不兼容”的错误提示,如下代码中的:@strWhere=@strWhere+' and PageUrl like '''+'%'+''+CONVERT(varchar(8000),@PageUrl)+''+'%'''
5、对于日期字段本来我想传入的是日期类型,所以直接进行比较,但结果没成功。原因是此处为进行字符串拼接,所以首先需要把传入的日期参数转化为字符串(如下面的:CONVERT(varchar(100),@StartLogTime,23)),然后再把此字符串转化为日期,当然这里的转化函数需要写到字符串常量里。如下面的:@strWhere=@strWhere+' and LogTime>=cast('+''''+CONVERT(varchar(100),@StartLogTime,23)+''''+' as datetime) ' 比如传入的日期为2010-12-01,则最后形成的SQL语句是:and LogTime>=cast('2010-12-01' as datetime)
USE [SRLogDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
------------------------------------
--用途:查询记录信息
------------------------------------
CREATE PROCEDURE [dbo].[Proc_Operation_Log_GetList]
@AppID int,
@UserName nvarchar(50),
@UserIP nvarchar(15),
@PageUrl ntext,
@Type nvarchar(100),
@StartLogTime datetime,
@EndLogTime datetime
AS
declare @strWhere varchar(8000)
declare @minDate datetime
declare @maxDate datetime
set @strWhere=''
set @minDate=CAST('1753-01-01' as datetime)
set @maxDate=CAST('9999-12-31' as datetime)
--数字类型匹配
if @AppID>0
begin
set @strWhere=@strWhere+' and AppID='+CONVERT(varchar(8000),@AppID)
end
--字符类型模糊查找
if @UserName<>''
begin
set @strWhere=@strWhere+' and UserName like '+''''+'%'+@UserName+'%'+''''
end
--字符类型完全匹配
if @UserIP<>''
begin
set @strWhere=@strWhere+' and UserIP='+''''+@UserIP+''''
end
--@PageUr为ntext类型,必须类型转换否则出现lntext 和varchar 在equal to 运算符中不兼容的问题
if CONVERT(varchar(8000),@PageUrl)<>''
begin
set @strWhere=@strWhere+' and PageUrl like '''+'%'+''+CONVERT(varchar(8000),@PageUrl)+''+'%'''
end
--日期比较
if @StartLogTime >@minDate
begin
set @strWhere=@strWhere+' and LogTime>=cast('+''''+CONVERT(varchar(100),@StartLogTime,23)+''''+' as datetime) '
end
if @StartLogTime <@maxDate
begin
set @strWhere=@strWhere+' and LogTime<=cast('+''''+CONVERT(varchar(100),@EndLogTime,23) +''''+' as datetime) '
end
declare @strMain varchar(8000)
set @strMain=' SELECT ID,UserID,UserName,UserIP,AppID,AppIP,Type,TypeDesc,PageForm,PageState,ObjectID,ObjectName,ObjectDesc,PageUrl,Result,ResultMsg,LogTime,ServerLogTime
FROM [Operation_Log]
WHERE 1=1'+@strWhere
exec(@strMain)
GO