存储过程手工分页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
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
