--资讯新闻百万级分页
(
@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