CREATE proc search_list @cmd nvarchar(20),@keywords nvarchar(50),@city_province nvarchar(50),@min_regdate nvarchar(50),@newstype nvarchar(50),@newsfenlei nvarchar(50),@fanwei int
as
select newsid,newstopic,newsneirong,newsmaker,news_start_time,news_end_time,newsmakerid,newsclicks,news_address,news_fenlei from newstable
where news_key like '%'+@keywords+'%'
and
( (@city_province<>'n' and news_address=@city_province)
or (@min_regdate<>'' and news_start_time>=cast('date()-@min_regdate' as datetime) and news_end_time>=cast(date()-@min_regdate as datetime))
or (@newstype<>'' and news_fenlei=@newstype)
or (@newsfenlei<>'' and ','+news_type+',' like '%,'+@newsfenlei+',%')
or (@fanwei=1 and newstopic like '%'+@keywords+'%')
or (newsneirong like '%'+@keywords+'%')
)
as
select newsid,newstopic,newsneirong,newsmaker,news_start_time,news_end_time,newsmakerid,newsclicks,news_address,news_fenlei from newstable
where news_key like '%'+@keywords+'%'
and
( (@city_province<>'n' and news_address=@city_province)
or (@min_regdate<>'' and news_start_time>=cast('date()-@min_regdate' as datetime) and news_end_time>=cast(date()-@min_regdate as datetime))
or (@newstype<>'' and news_fenlei=@newstype)
or (@newsfenlei<>'' and ','+news_type+',' like '%,'+@newsfenlei+',%')
or (@fanwei=1 and newstopic like '%'+@keywords+'%')
or (newsneirong like '%'+@keywords+'%')
)
as
BEGIN
DECLARE @str VARCHAR(800)
SET @str='select newsid,newstopic,newsneirong,newsmaker,news_start_time,news_end_time,newsmakerid,newsclicks,news_address,news_fenlei from newstable where news_key like ''%@keywords%'''
if @city_province<>"n"
SET @str = @str+' and news_address='+@city_province
if @min_regdate<>" "
SET @str = @str +' and news_start_time>=cast(getdate()-'+@min_regdate+' as datetime) and news_end_time>=cast(date()-'+@min_regdate+' as datetime)'
if @newstype<>" "
SET @str = @str +' and news_fenlei='+@newstype
if @newsfenlei<>" "
SET @str = @str +' and '',''+news_type+'','' like ''%,'+@newsfenlei+',%'''
if @fanwei=1
SET @str = @str +' and newstopic like ''%'+@keywords+'%'''
else
SET @str = @str +' and newsneirong like ''%'+@keywords+'%'''EXEC(@str)END
CREATE proc search_list @cmd nvarchar(20),@keywords nvarchar(50),@city_province nvarchar(50),@min_regdate nvarchar(50),@newstype nvarchar(50),@newsfenlei nvarchar(50),@fanwei int
as
select newsid,newstopic,newsneirong,newsmaker,news_start_time,news_end_time,newsmakerid,newsclicks,news_address,news_fenlei from newstable
where news_key like '%'+@keywords+'%'
and
( (@city_province<>'n' and news_address=@city_province)
or (@min_regdate<>'' and news_start_time>=cast(getdate()-@min_regdate as datetime) and news_end_time>=cast(getdate()-@min_regdate as datetime))
or (@newstype<>'' and news_fenlei=@newstype)
or (@newsfenlei<>'' and ','+news_type+',' like '%,'+@newsfenlei+',%')
or (@fanwei=1 and newstopic like '%'+@keywords+'%')
or (newsneirong like '%'+@keywords+'%')
)