分页存储过程
**//*
说明:1.支持多表查询 2.支持任意排序 3.不支持表别名
参考了
evafly920:[分享]千万数量级分页存储过程(效果演示)
地址:http://blog.csdn.net/evafly920/archive/2006/03/03/614813.aspx
![]()
IF(EXISTS(SELECT * FROM sysobjects WHERE [id]=OBJECT_ID('usp_PagingLarge') AND xtype='P'))
DROP PROCEDURE usp_PagingLarge
*/
![]()
GO
![]()
CREATE PROCEDURE usp_PagingLarge
@TableNames VARCHAR(200), --表名,可以是多个表,但不能用别名
@PrimaryKey VARCHAR(100), --主键,可以为空,但@Order为空时该值不能为空
@Fields VARCHAR(200), --要取出的字段,可以是多个表的字段,可以为空,为空表示select *
@PageSize INT, --每页记录数
@CurrentPage INT, --当前页,0表示第1页
@Filter VARCHAR(200) = '', --条件,可以为空,不用填 where
@Group VARCHAR(200) = '', --分组依据,可以为空,不用填 group by
@Order VARCHAR(200) = '' --排序,可以为空,为空默认按主键升序排列,不用填 order by
AS
BEGIN
DECLARE @SortColumn VARCHAR(200)
DECLARE @Operator CHAR(2)
DECLARE @SortTable VARCHAR(200)
DECLARE @SortName VARCHAR(200)
IF @Fields = ''
SET @Fields = '*'
IF @Filter = ''
SET @Filter = 'WHERE 1=1'
ELSE
SET @Filter = 'WHERE ' + @Filter
IF @Group <>''
SET @Group = 'GROUP BY ' + @Group
![]()
IF @Order <> ''
BEGIN
DECLARE @pos1 INT, @pos2 INT
SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC')
IF CHARINDEX(' DESC', @Order) > 0
IF CHARINDEX(' ASC', @Order) > 0
BEGIN
IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order)
SET @Operator = '<='
ELSE
SET @Operator = '>='
END
ELSE
SET @Operator = '<='
ELSE
SET @Operator = '>='
SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '')
SET @pos1 = CHARINDEX(',', @SortColumn)
IF @pos1 > 0
SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1)
SET @pos2 = CHARINDEX('.', @SortColumn)
IF @pos2 > 0
BEGIN
SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1)
IF @pos1 > 0
SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1)
ELSE
SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2)
END
ELSE
BEGIN
SET @SortTable = @TableNames
SET @SortName = @SortColumn
END
END
ELSE
BEGIN
SET @SortColumn = @PrimaryKey
SET @SortTable = @TableNames
SET @SortName = @SortColumn
SET @Order = @SortColumn
SET @Operator = '>='
END
![]()
DECLARE @type varchar(50)
DECLARE @prec int
SELECT @type=t.name, @prec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName
IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
![]()
DECLARE @TopRows INT
SET @TopRows = @PageSize * @CurrentPage + 1
print @TopRows
print @Operator
EXEC('
DECLARE @SortColumnBegin ' + @type + '
SET ROWCOUNT ' + @TopRows + '
SELECT @SortColumnBegin=' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + '
SET ROWCOUNT ' + @PageSize + '
SELECT ' + @Fields + ' FROM ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + '
')
END
![]()
GO
![]()
--调用例子:
--1.单表/单排序
EXEC usp_PagingLarge 'bigtable','d_id','d_id,d_title,d_content,d_time',20,1,'','','d_id desc'
--2.单表/多排序
EXEC usp_PagingLarge 'bigtable','d_id','*',20,0,'','','d_time asc,d_id desc'
--3.多表/单排序
EXEC usp_PagingLarge 'bigtable left join bigtable_author on bigtable.d_id=bigtable_author.BigTable_id', 'bigtable.d_id', 'bigtable.d_id,bigtable.d_title,bigtable.d_content,bigtable.d_time,bigtable_author.d_author', 20, 0, '', '', 'bigtable.d_id asc'
--4.多表/多排序
EXEC usp_PagingLarge 'bigtable left join bigtable_author on bigtable.d_id=bigtable_author.BigTable_id', 'bigtable.d_id', 'bigtable.d_id,bigtable.d_title,bigtable.d_content,bigtable.d_time,bigtable_author.d_author', 20, 0, '', '', 'bigtable.d_time asc,bigtable.d_id desc'
/Files/jiny-z/Paging_Custom.rar
alter PROCEDURE SP_Pagination
/*
***************************************************************
** 分页存储过程 **
***************************************************************
参数说明:
1.Tables :表名称,视图
2.PrimaryKey :主关键字
3.Sort :排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc
4.CurrentPage :当前页码
5.PageSize :分页尺寸
6.Fields:字段
6.Filter :过滤语句,不带Where
7.Group :Group语句,不带Group By
8.docount: 1返回总行数,0返回列表
![]()
***************************************************************/
(
@Tables varchar(1000),
@PrimaryKey varchar(100),
@Sort varchar(200) = NULL,
@CurrentPage int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL,
@docount bit = 0
)
AS
/*默认排序*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int
![]()
![]()
/*设定排序语句.*/
IF CHARINDEX('DESC',@Sort)>0
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
SET @operator = '<='
END
ELSE
BEGIN
IF CHARINDEX('ASC', @Sort) = 0
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
SET @operator = '>='
END
IF CHARINDEX('.', @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END
![]()
SELECT @type=t.name, @prec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName
![]()
IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
![]()
DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(200)
DECLARE @strSimpleFilter varchar(200)
DECLARE @strGroup varchar(200)
![]()
/*默认当前页*/
IF @CurrentPage < 1
SET @CurrentPage = 1
![]()
/*设置分页参数.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))
![]()
/*筛选以及分组语句.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
![]()
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''
![]()
![]()
declare @cTemp NVarChar(1000)
declare @PageCount int, @lineCount decimal
![]()
CREATE TABLE #temp(linecount INT)
![]()
set @cTemp = 'insert into #temp (linecount) select count(*) FROM ' + @Tables + @strFilter + ' ' + @strGroup
exec (@cTemp)
![]()
select @lineCount = linecount from #temp
![]()
drop table #temp
![]()
![]()
if(@docount=1)
begin
select @lineCount '总行数'
end
else
![]()
begin
--得到总页数
set @PageCount = CEILING(@lineCount/@strPageSize)
if @CurrentPage > @PageCount
begin
set @cTemp = 'SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE 1>2 '
end
else
begin
/*执行查询语句*/
set @cTemp = 'DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ''
end
![]()
--print @cTemp
EXEC(@cTemp)
end
![]()
-----------------------------------------------------
![]()
分页查询的方法已经很多很多,在这里我也加入成为其中一员。
SQL Server中有一个Set Rowcount的的设置,它的意思是使命令的处理在响应指定的行数之后停止处理命令,利用这个特点,我们可以借用它来在一个千万行级数据表中实现高性能分页查询。先来说说实现方式:
1、我们来假定Table中有一个已经建立了索引的主键字段ID(整数型),我们将按照这个字段来取数据进行分页。
2、页的大小我们放在@PageSize中
3、当前页号我们放在@CurrentPage中
4、如何让记录指针快速滚动到我们要取的数据开头的那一行呢,这是关键所在!有了Set RowCount,我们就很容易实现了。
5、如果我们成功地滚动记录指针到我们要取的数据的开头的那一行,然后我们把那一行的记录的ID字段的值记录下来,那么,利用Top和条件,我们就很容易的得到指定页的数据了。当然,有了Set RowCount,我们难道还用Top么?
看看Set Rowcount怎么来帮我们的忙吧:
![]()
Declare @ID int
Declare @MoveRecords int
![]()
--@CurrentPage和@PageSize是传入参数
Set @MoveRecords=@CurrentPage * @PageSize+1
![]()
--下面两行实现快速滚动到我们要取的数据的行,并把ID记录下来
Set Rowcount @MoveRecords
Select @ID=ID from Table1 Order by ID
![]()
Set Rowcount @PageSize
--最恨为了减少麻烦使用*了,但是在这里为了说明方便,暂时用一下
Select * From Table1 Where ID>=@ID Order By ID
Set Rowcount 0
大家可以试试看,在一个1千W记录的表里面,一下子方翻页到第100页(每页100条),看看有多快!
分享]千万数量级分页存储过程(带效果演示)
效果演示:http://www.cn5135.com/_App/Opportunities/QueryResult.aspx
![]()
CREATE PROCEDURE CN5135_SP_Pagination
/*
***************************************************************
** 千万数量级分页存储过程 **
***************************************************************
参数说明:
1.Tables :表名称,视图
2.PrimaryKey :主关键字
3.Sort :排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc
4.CurrentPage :当前页码
5.PageSize :分页尺寸
6.Filter :过滤语句,不带Where
7.Group :Group语句,不带Group By
效果演示:http://www.cn5135.com/_App/Opportunities/QueryResult.aspx
***************************************************************/
(
@Tables varchar(1000),
@PrimaryKey varchar(100),
@Sort varchar(200) = NULL,
@CurrentPage int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL
)
AS
/*默认排序*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int
/*设定排序语句.*/
IF CHARINDEX('DESC',@Sort)>0
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
SET @operator = '<='
END
ELSE
BEGIN
IF CHARINDEX('ASC', @Sort) = 0
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
SET @operator = '>='
END
IF CHARINDEX('.', @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END
SELECT @type=t.name, @prec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName
IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)
/*默认当前页*/
IF @CurrentPage < 1
SET @CurrentPage = 1
/*设置分页参数.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))
/*筛选以及分组语句.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''
/*执行查询语句*/
EXEC(
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
'
)
GO
邹建的存储过程
ALTER PROC PageView
@tbname sysname, --要分页显示的表名
@FieldKey sysname, --用于定位记录的主键(惟一键)字段,只能是单个字段
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小(记录数)
@FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
--用于指定排序顺序
@Where nvarchar(1000)='', --查询条件
@PageCount int OUTPUT --总页数
AS
DECLARE @sql nvarchar(4000)
SET NOCOUNT ON
--检查对象是否有效
IF OBJECT_ID(@tbname) IS NULL
BEGIN
RAISERROR(N'对象"%s"不存在',1,16,@tbname)
RETURN
END
IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
BEGIN
RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)
RETURN
END
![]()
--分页字段检查
IF ISNULL(@FieldKey,N'')=''
BEGIN
RAISERROR(N'分页处理需要主键(或者惟一键)',1,16)
RETURN
END
![]()
--其他参数检查及规范
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@FieldOrder,N'')=N''
SET @FieldOrder=N''
ELSE
SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
IF ISNULL(@Where,N'')=N''
SET @Where=N''
ELSE
SET @Where=N'WHERE ('+@Where+N')'
![]()
--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL
BEGIN
SET @sql=N'SELECT @PageCount=COUNT(*)'
与自定义分页结合例子:
/Files/jiny-z/Paging_Custom.rar
注:
1)如表名参数为多表连接时,sort列必须指定表名;
2)只支持单字段排序,有朋友如果问为什么不做成可以多字段排序的,理论上确实有这种可能性,但需要以一定的效率损失为代价,而且会使方法过于复杂,如真有这种需要,完全可以写一个单独的分页存储过程,无论在性能还是复杂度上都比通用要简单.
3) 对非unique字段排序的支持,但必须设定一个PK字段(注:只要是unique字段都可以作为pk字段)
1
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Paging_RowCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
2
drop procedure [dbo].[Paging_RowCount]
3
GO
4![]()
5
SET QUOTED_IDENTIFIER ON
6
GO
7
SET ANSI_NULLS ON
8
GO
9
---------------------------------------------------------------
10
-- 分页存储过程(使用RowCount) --edit by SiBen
11
-- summary:
12
-- 获取表或表集合的分页数据
13
-- 当多表连接时,sort列必须指定表名
14
---------------------------------------------------------------
15![]()
16
CREATE PROCEDURE Paging_RowCount
17
(
18
@Tables varchar(1000),
19
@PK varchar(100),
20
@Sort varchar(200) = NULL,
21
@PageNumber int = 1,
22
@PageSize int = 10,
23
@Fields varchar(1000) = '*',
24
@Filter varchar(1000) = NULL,
25
@Group varchar(1000) = NULL,
26
@RecordCount int = 0 output
27
)
28
AS
29![]()
30
/*Default Sorting*/
31
IF @Sort IS NULL OR @Sort = ''
32
SET @Sort = @PK
33![]()
34
/*Find the @PK type*/
35
DECLARE @SortTable varchar(100)
36
DECLARE @SortName varchar(100)
37
DECLARE @strSortColumn varchar(200)
38
DECLARE @operator char(2)
39
DECLARE @type varchar(100)
40
DECLARE @prec int
41![]()
42
/*Set sorting variables.*/
43
IF CHARINDEX('DESC',@Sort)>0
44
BEGIN
45
SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
46
SET @operator = '<'
47
END
48
ELSE
49
BEGIN
50
IF CHARINDEX('ASC', @Sort) > 0
51
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
52
ELSE
53
SET @strSortColumn = @Sort
54![]()
55
SET @operator = '>'
56
END
57![]()
58![]()
59
IF CHARINDEX('.', @strSortColumn) > 0
60
BEGIN
61
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
62
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
63
END
64
ELSE
65
BEGIN
66
SET @SortTable = @Tables
67
SET @SortName = @strSortColumn
68
END
69![]()
70
SELECT @type=t.name, @prec=c.prec
71
FROM sysobjects o
72
JOIN syscolumns c on o.id=c.id
73
JOIN systypes t on c.xusertype=t.xusertype
74
WHERE o.name = @SortTable AND c.name = @SortName
75![]()
76
IF CHARINDEX('char', @type) > 0
77
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
78![]()
79
DECLARE @strPageSize varchar(50)
80
DECLARE @strStartRow varchar(50)
81
DECLARE @strFilter varchar(1000)
82
DECLARE @strSimpleFilter varchar(1000)
83
DECLARE @strGroup varchar(1000)
84![]()
85
/*Default Page Number*/
86
IF @PageNumber < 1
87
SET @PageNumber = 1
88![]()
89
/*Set paging variables.*/
90
SET @strPageSize = CAST(@PageSize AS varchar(50))
91
SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS varchar(50))
92![]()
93
/*Set filter & group variables.*/
94
IF @Filter IS NOT NULL AND @Filter != ''
95
BEGIN
96
SET @strFilter = ' WHERE ' + @Filter + ' '
97
SET @strSimpleFilter = ' AND ' + @Filter + ' '
98
END
99
ELSE
100
BEGIN
101
SET @strSimpleFilter = ''
102
SET @strFilter = ''
103
END
104
IF @Group IS NOT NULL AND @Group != ''
105
SET @strGroup = ' GROUP BY ' + @Group + ' '
106
ELSE
107
SET @strGroup = ''
108![]()
109
/*Get rows count.*/
110
DECLARE @str_Count_SQL nvarchar(500)
111
SET @str_Count_SQL= 'SELECT @TotalCount=count(*) FROM ' + @Tables + @strFilter
112
EXEC sp_executesql @str_Count_SQL,N'@TotalCount int=0 output',@RecordCount output
113
114
/*Execute dynamic query*/
115
IF @Sort = @PK
116
BEGIN
117
EXEC(
118
'
119
DECLARE @SortColumn ' + @type + '
120
SET ROWCOUNT ' + @strStartRow + '
121
SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
122
SET ROWCOUNT ' + @strPageSize + '
123
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + '= @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
124
'
125
)
126
END
127
ELSE
128
BEGIN
129
EXEC(
130
'
131
DECLARE @SortColumn ' + @type + '
132
DECLARE @SortNullValue ' + @type + '
133
DECLARE @PKStartValue int
134
SET @SortNullValue=CAST('''' as '+ @type +')
135
SET ROWCOUNT ' + @strStartRow + '
136
SELECT @SortColumn= isNull(' + @strSortColumn + ',@SortNullValue), @PKStartValue = '+ @PK +' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ','+ @PK +' Desc
137
SET ROWCOUNT ' + @strPageSize + '
138
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE (isNull(' + @strSortColumn+',@SortNullValue)' + @operator + ' @SortColumn or (isNull(' + @strSortColumn+',@SortNullValue)=@SortColumn and '+ @PK +'<=@PKStartValue))' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ','+ @PK +' Desc
139
'
140
)
141
END
142
GO
143
SET QUOTED_IDENTIFIER OFF
144
GO
145
SET ANSI_NULLS ON
146
GO
147![]()
148![]()
http://www.codeproject.com/aspnet/PagingLarge.asp 2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148