数据库多条件自定义查询

数据库多条件自定义查询

USE [DeviceManageSystem]
GO
/****** 对象:  StoredProcedure [dbo].[News_Select]    脚本日期: 04/19/2012 21:42:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[News_Select] 
(

@newsTitle 	nvarchar(50),
@newsContent	nvarchar(1000),
@firstDateAfter	datetime,
@firstDateBefore	datetime,
@lastDateAfter	datetime,
@lastDateBefore	datetime,
@author	nvarchar(50),
@adminName nvarchar(20)
) 
AS 

DECLARE @sql nvarchar(4000)

DECLARE @WhereClause nvarchar(2000)



begin

SET @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)>0
set @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,adminName
from tbl_News   ' +
@WhereClause 
+ ' order by lastDate desc' 


EXEC sp_executesql @sql
END