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