存储过程手工分页sql

CREATE procedure News_Class

--资讯新闻百万级分页

(

@StrWhere varchar(100),--条件

@PageSize int, --页面大小

@PageIndex int --页面索引

)

AS

declare @strSQL varchar(2000) -- 主语句

declare @strCountSQL varchar(2000) -- 总记录主语句

declare @strTmp varchar(1000) -- 临时变量

Set @strTmp =' Select top '+ str(@PageSize) + ' Title,AddTime from Tb_News ' --此处注意，需几个字段读几个字段

if @StrWhere<>''

Begin

Set @strSQL=@strTmp + ' where ID < (select min(ID) from (select top ' + str((@PageIndex-1)*@PageSize)+' ID from Tb_News Where '+@StrWhere+' order by ID desc) as tblTmp ) and '+@StrWhere+' order by ID desc'

set @strCountSQL='select count(ID) as countx from Tb_News Where '+@StrWhere+' '

End

else

Begin

Set @strSQL=@strTmp + ' where ID < (select min(ID) from (select top ' + str((@PageIndex-1)*@PageSize)+' ID from Tb_News order by ID desc) as tblTmp ) order by ID desc'

set @strCountSQL='select count(ID) as countx from Tb_News '

End

if @PageIndex = 1

if @StrWhere<>''

Begin

Set @strSQL=@strTmp +' Where '+@StrWhere+'order by ID desc'

End

else

Begin

Set @strSQL=@strTmp +'order by ID desc'

End

exec (@strSQL)

exec (@strCountSQL)

GO

转载于:https://www.cnblogs.com/xxsmile/p/4422475.html