今天在做基于ajaxpro分页的时候,发现如果按照前一篇文章所指示,和gridview交互很是不方便,于是就写了基于存储过程和DataTable的分页,主要是存储过程,我就把存储过程给出,其他的很简单,就不再列出
1
--分页存储过程
2
create procedure GetNews
3
(
4
@type int,--文章的类型
5
@pgs int,--页大小
6
@pgn int--页码,页码为0为求总也数
7
)
8
as
9
declare @strsql nvarchar(500) --执行的sql语句
10
declare @zongshu int--纪录总数
11
declare @yeshu int --总页数
12
declare @strtemp int--临时变量
13
if @pgn=0
14
begin
15
select @zongshu=count(*) from LYNews where newstype=@type
16
if @zongshu<=@pgs
17
begin
18
if @zongshu=0
19![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
set @yeshu=0
21
22
else
23
set @yeshu=1
24
end
25
else--如果@zongshu>@pgs那么开始计算分页,如果@zongshu%@pgs=0的话,就说明每页都是慢的,如果不是的话,那么将存在多余的占一页
26
begin
27
set @strtemp=@zongshu%@pgs --如果@zongshu%@pgs=0的话,就说明每页都是满的,如果不是的话,那么将存在多余的纪录占一页
28
if @strtemp=0
29
30
set @yeshu=@zongshu/@pgs
31
32
else
33
begin
34
set @yeshu=@zongshu/@pgs+1--否则的话那么得在添加一页来显示多余的数据
35
end
36
set @strsql='select '+str(@zongshu)+' as zongshu, '+str(@yeshu)+' as yeshu' --返回总页数和总纪录数
37
end
38
end
39
else
40
begin
41
if @pgn=1
42
43
set @strsql='select top '+str(@pgs)+' * from Lynews where newstype='+str(@type)+' order by postdate desc'--返回第一页的纪录
44
45
else
46
47
set @strsql='select top '+str(@pgs)+' * from LyNews Where newstype='+str(@type)+' and postdate<(select min(postdate) from (select top '+str((@pgn-1)*@pgs)+' * from LyNews where newstype='+str(@type)+' order by postdate desc ) as t) order by postdate desc'
48
49
end
50
exec (@strsql)
51
go
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
47
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
48
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
49
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
50
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
51
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)