--用户管理分页 --参数@NewsTypeID 新闻类型 --参数@NewsStatus 新闻状态 --参数@KeyWord 关键字 --参数@Promulgator 发布者 --参数@InTime 发布时间 -- @docount=1 返回记录统计 Alter procedure PR_GetNews ( @KeyWord varchar(100), @NewsTypeID varchar(8), @NewsStatus int, @Promulgator varchar(50), @InTime datetime, @pagesize int, @pageindex int, @docount bit ) as set nocount on declare @where nvarchar(400) set @where='where 1=1' if @KeyWord <>''set @where = @where+' and (Content like ''%'+@KeyWord+'%'' or Title like ''%'+@KeyWord+'%'')' if @NewsTypeID <>''set @where=@where+' and NewsTypeID like ''%'+@NewsTypeID+'%'''--使用模糊查询因为一条新闻可能是多个类型 if @NewsStatus >=0set @where=@where+' and NewsStatus= '+str(@NewsStatus,1) +'' if @Promulgator <>''set @where = @where+' and Promulgator like ''%'+ @Promulgator +'%''' if @InTime is not nullset @where = @where +' and (NewsCheckInTime between '''+convert(varchar(10),@InTime,120)+''' and '''+convert(varchar(10),GetDate()+1,120)+''')' declare @RecordCount int declare @TempSql nvarchar(1000) print @where set @TempSql='select @RecordCount=count(NewsID) from TB_News '+ @where exec sp_executesql @TempSql,N'@RecordCount int output',@RecordCount output if(@docount=1) select @RecordCount else begin if(@pageindex=1) exec('select top '+@pagesize+' * from TB_News '+@where+' order by NewsID desc') else begin declare @PageUpperBound int declare @endrecords int set @PageUpperBound=@pageindex*@pagesize if(@PageUpperBound-@pagesize)>=@RecordCount select '' elseif(@RecordCount-(@PageUpperBound-@pagesize)<=@pagesize) begin set @endrecords=@RecordCount-(@PageUpperBound-@pagesize) exec('select * from (select top '+@endrecords+' * from TB_News '+@where+' order by NewsID )A order by NewsID desc') end else exec('select * from (select top '+@pagesize+'* from (select top '+@PageUpperBound+' * from TB_News '+@where+' order by NewsID desc)A order by NewsID )B order by NewsID desc') end end set nocount off --select @where GO