数据库多条件自定义查询

数据库多条件自定义查询

USE [DeviceManageSystem]GO/****** 对象:  StoredProcedure [dbo].[News_Select]    脚本日期: 04/19/2012 21:42:40 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[News_Select] (@newsTitle nvarchar(50),@newsContentnvarchar(1000),@firstDateAfterdatetime,@firstDateBeforedatetime,@lastDateAfterdatetime,@lastDateBeforedatetime,@authornvarchar(50),@adminName nvarchar(20)) AS DECLARE @sql nvarchar(4000)DECLARE @WhereClause nvarchar(2000)beginSET @WhereClause = 'WHERE --'if LEN(@newsTitle) > 0 SET @WhereClause = @WhereClause + 'AND ([newsTitle] like ''%' + @newsTitle + '%'')'if LEN(@newsContent) > 0 SET @WhereClause = @WhereClause+ 'AND ([newsContent] like ''%' + @newsContent + '%'' )'if not (@firstDateAfter is null) SET @WhereClause = @WhereClause+ 'AND (([firstDate] is null) or ([firstDate] >= CAST(''' + CAST(@firstDateAfter as nvarchar) + '''  AS datetime)))'if not (@firstDateBefore is null) SET @WhereClause = @WhereClause+ 'AND (([firstDate] is null) or ([firstDate] <= CAST(''' + CAST(@firstDateBefore as nvarchar) + '''  AS datetime)))'if not (@lastDateAfter is null) SET @WhereClause = @WhereClause+ 'AND (([lastDate] is null) or ([lastDate] >= CAST( ''' + CAST(@lastDateAfter as nvarchar) + '''  AS datetime)))'if not (@lastDateBefore is null) SET @WhereClause = @WhereClause+ 'AND (([lastDate] is null) or ([lastDate] <= CAST( ''' + CAST(@lastDateBefore as nvarchar) + '''  AS datetime)))'if len(@author)>0set @WhereClause=@WhereClause + ' and ([author] like ''%' + @author +  '%'')'if not (@adminName is null) SET @WhereClause = @WhereClause + 'AND ([adminName] like ''%' +  @adminName + '%'' )'if (@WhereClause = 'WHERE --')SET @WhereClause = ' 'SET @sql = 'select newsId,newsTitle,newsContent,firstDate,lastDate,author,adminNamefrom tbl_News   ' +@WhereClause + ' order by lastDate desc' EXEC sp_executesql @sqlEND