含多项查询条件的存储过程（SQL2008）

 在做一个搜索时，有多项搜索条件，如果这么多条件单独在程序里判断是很麻烦的。今天花几个小时的时间才做好这个存储过程，用这么长别笑话我笨啊，呵呵，发现很多基础的语法都忘了，当然用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