自己写的一个SQL分页存储过程,原理来自网上的一篇文章,
原理为:
原理为:
select
top
页大小
*
from table1
where id >
( select max (id) from
( select top ((页码 - 1 ) * 页大小) id from table1 order by id) as T
)
from table1
where id >
( select max (id) from
( select top ((页码 - 1 ) * 页大小) id from table1 order by id) as T
)
CREATE
procedure
GetLYZXNews
@type int , -- 文章类型
@Pgs int , -- 页大小
@Pgn int -- 页码,页码为0则为求总页数
as
declare @strsql varchar ( 500 ) -- 执行的SQL语句
declare @ZongShu int -- 记录总数
declare @YeShu int -- 总页数
declare @strTmp int -- 临时变量
if @Pgn = 0
begin
select @ZongShu = count ( * ) from LYNews where type = @type
if @ZongShu <= @Pgs
begin
if @ZongShu = 0
begin
set @YeShu = 0
end
else
begin
Set @YeShu = 1
end
end
else
begin
set @strTmp = @ZongShu % @Pgs
if @strTmp = 0
begin
set @YeShu = @ZongShu / @Pgs
end
else
begin
set @YeShu = @ZongShu / @Pgs + 1
end
end
set @strsql = " select " + str ( @ZongShu ) + " as ZongShu," + str ( @YeShu ) + " as YeShu"
end
else
begin
if @Pgn = 1
begin
set @strsql = " select top " + str ( @Pgs ) + " * from LyNews Where type = " + str ( @type ) + " order by postDate desc "
end
else
begin
set @strsql = " select top " + str ( @Pgs ) + " * from LyNews Where type = " + str ( @type ) + " and postDate < ( select min (postDate) from "
set @strsql = @strsql + "( select top " + str (( @Pgn - 1 ) * @Pgs ) + " * from LyNews where type = " + str ( @type ) + " order by postdate desc ) as t) order by postDate desc "
end
end
exec ( @strSQL )
GO
@type int , -- 文章类型
@Pgs int , -- 页大小
@Pgn int -- 页码,页码为0则为求总页数
as
declare @strsql varchar ( 500 ) -- 执行的SQL语句
declare @ZongShu int -- 记录总数
declare @YeShu int -- 总页数
declare @strTmp int -- 临时变量
if @Pgn = 0
begin
select @ZongShu = count ( * ) from LYNews where type = @type
if @ZongShu <= @Pgs
begin
if @ZongShu = 0
begin
set @YeShu = 0
end
else
begin
Set @YeShu = 1
end
end
else
begin
set @strTmp = @ZongShu % @Pgs
if @strTmp = 0
begin
set @YeShu = @ZongShu / @Pgs
end
else
begin
set @YeShu = @ZongShu / @Pgs + 1
end
end
set @strsql = " select " + str ( @ZongShu ) + " as ZongShu," + str ( @YeShu ) + " as YeShu"
end
else
begin
if @Pgn = 1
begin
set @strsql = " select top " + str ( @Pgs ) + " * from LyNews Where type = " + str ( @type ) + " order by postDate desc "
end
else
begin
set @strsql = " select top " + str ( @Pgs ) + " * from LyNews Where type = " + str ( @type ) + " and postDate < ( select min (postDate) from "
set @strsql = @strsql + "( select top " + str (( @Pgn - 1 ) * @Pgs ) + " * from LyNews where type = " + str ( @type ) + " order by postdate desc ) as t) order by postDate desc "
end
end
exec ( @strSQL )
GO