SQL2000分页存储过程
1
set ANSI_NULLS ON
2
set QUOTED_IDENTIFIER ON
3
GO
4![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
/***********************************************
6
名称:Proc_GetPageList
7
功能:分页存储过程
8
创建人:kangaroo
9
日期:2008/7/17
10
***********************************************/
11
ALTER PROCEDURE [dbo].[Proc_GetPageList]
12
(
13
@Tables varchar(1000), --表名
14
@PK varchar(100), --主键
15
@Fields varchar(1000) = '*', --查询的字段
16
@SortField varchar(200) = NULL, --排序的字段
17
@SortType varchar(10)= 'ASC', --排序方式
18
@PageIndex int = 1, --页码索引
19
@PageSize int = 10, --页码大小
20
@Filter varchar(1000) = NULL, --过滤条件
21
@PageCount int = 1 output, ----查询结果分页后的总页数
22
@RecordCounts int = 1 output ----查询到的记录数
23
)
24
AS
25
SET NOCOUNT ON
26
declare @FieldName nvarchar(50)
27
declare @SqlSort nvarchar(250)
28
declare @SqlCount nvarchar(2000)
29
declare @FromTemp nvarchar(1000)
30
declare @SqlResult nvarchar(4000)
31
declare @PageMinBound int
32
declare @PageMaxBound int
33
--------首先生成排序方法---------
34
if @SortType='ASC' --升序
35
begin
36
if not(@SortField is null)
37
set @SqlSort = ' Order by ' + @SortField
38
else
39
set @SqlSort = ' Order by ' + @PK
40
end
41
else --降序
42
begin
43
if not(@SortField is null)
44
set @SqlSort = ' Order by ' + @SortField + ' DESC'
45
else
46
set @SqlSort = ' Order by ' + @PK + ' DESC '
47
end
48
--------生成查询语句--------
49
if @Filter is null or @Filter='' --没有设置显示条件
50
begin
51
set @FromTemp = ' From ' + @Tables
52
end
53
else
54
begin
55
set @FromTemp = ' From ' + @Tables + ' where ' + @Filter
56
end
57
set @SqlCount= 'select @RecordCounts=Count(' + @PK + ') '+@FromTemp
58
----取得查询结果总数量-----
59
exec sp_executesql @SqlCount,N'@RecordCounts int out ',@RecordCounts out
60![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
61
--取得分页总数
62
if @RecordCounts<= @PageSize
63
set @PageCount = 1
64
else
65
set @PageCount = (@RecordCounts / @PageSize) + 1
66![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
67
if @PageIndex=1
68
begin --第一页用TOP方法来获取当前页记录
69
set @SqlResult='select top '+str(@PageSize)+' '+@Fields+' '+@FromTemp+' '+@SqlSort
70
end
71![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
72
else
73
begin --用临时表获取当前页计录
74
set @PageMinBound=(@Pageindex-1)*@Pagesize
75
set @PageMaxBound=@PageMinBound+@Pagesize
76
create table #Pageindex (id int identity(1,1) not null,nid int)
77
set rowcount @PageMaxBound
78
set @SqlResult='insert into #Pageindex(nid) select '+@PK+' '+@FromTemp+' '+@SqlSort
79
set @SqlResult=@SqlResult+' select '+@Fields+' from '+@Tables+' a,#Pageindex p
80
where a.'+@PK+'=p.nid and p.id>'+str(@PageMinBound)+' and p.id<='+str(@PageMaxBound)
81![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
82
end
83
print @SqlResult
84
exec sp_executesql @SqlResult
85![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
86![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
87![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](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/ExpandedBlockStart.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.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)
52
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
53
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
54
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
55
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
56
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
57
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
58
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
59
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
60
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
61
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
62
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
63
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
64
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
65
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
66
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
67
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
68
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
69
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
70
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
71
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
72
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
73
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
74
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
75
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
76
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
77
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
78
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
79
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
80
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
81
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
82
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
83
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
84
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
85
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
86
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
87
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)