Question

SQL 存储过程分页，（要求百万级）

适用于sql2000/2005功能强大，方便使用请大家积极回答。分不够还会加分的。不过请不要把不管用的代码贴上来。

Answer 1

CREATE PROCEDURE SP_Pagination
@TableNames VARCHAR(200), --表名，可以是多个表，但不能用别名
@PrimaryKey VARCHAR(100), --主键，可以为空，但@Order为空时该值不能为空
@Fields VARCHAR(200) = '*', --要取出的字段，可以是多个表的字段，可以为空，为空表示select *
@PageSize int = 30, --每页记录数
@CurrentPage int = 1, --当前页，0表示第1页
@IsCount int = 0,
@Filter VARCHAR(2000) = '', --条件，可以为空，不用填 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)
DECLARE @sql nvarchar(4000)
DECLARE @iPageCount int
IF @Fields = ''
SET @Fields = '*'
IF @Filter = ''
SET @Filter = 'Where 1=1'
ELSE
SET @Filter = 'Where ' + @Filter
IF @Group <>''
SET @Group = 'GROUP BY ' + @Group

IF @IsCount != 0
BEGIN
SET @Sql = 'SELECT @iPageCount=COUNT(1) FROM ' + @TableNames + ' ' + @Filter + ''
EXEC sp_executesql @Sql,N'@iPageCount int OUTPUT',@iPageCount OUTPUT--计算总记录数
END

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 + '
')
If @@Error = 0
BEGIN
RETURN @iPageCount ---返回记录总数
END
END
GO

Answer 2

2000以前做过，因为微软的向下兼容，所以2005也能用
表名和id自己改下,或者用变量带进去：

create proc MyPage @page int
as
begin
if(@page<=(select case when count(*)%10>0 then count(*)/10+1 else count(*)/10 end from aaa) and @page>0)
begin
declare @begin int,@end int
select @begin=(select case when @page>count(*)/10 then Count(*) else @page*10 end from aaa)
declare @sql varchar(8000)
select @sql='select top '+cast(@begin as varchar(20))+' * from aaa order by id'
select @end=(select case when @page>count(*)/10 then count(*)%10 else 10 end from aaa)
select @sql='select * from (select top '+cast(@end as varchar(20))+' * from ('+@sql+') aa order by id desc)bb order by id'
exec(@sql)
end
else
select '对不起，输入的页码超过范围'
end

测试：
--插入测试数据
create table aaa(id int primary key identity(1,1),test varchar(10))

insert into aaa
select 'a' union all
select 'b' union all
select 'b' union all
select 'c' union all
select 'd' union all
select 'e' union all
select 'f' union all
select 'g' union all
select 'h' union all
select 'i' union all
select 'j' union all
select 'k' union all
select 'l' union all
select 'm' union all
select 'n' union all
select 'o' union all
select 'p' union all
select 'q' union all
select 'r' union all
select 's' union all
select 't' union all
select 'u' union all
select 'v' union all
select 'w' union all
select 'x' union all
select 'y' union all
select 'z'

--测试数据查询
id test
----------- ----------
1 a
2 b
3 b
4 c
5 d
6 e
7 f
8 g
9 h
10 i
11 j
12 k
13 l
14 m
15 n
16 o
17 p
18 q
19 r
20 s
21 t
22 u
23 v
24 w
25 x
26 y
27 z

(所影响的行数为 27 行)

--测试
exec Mypage 1

id test
----------- ----------
1 a
2 b
3 b
4 c
5 d
6 e
7 f
8 g
9 h
10 i

(所影响的行数为 10 行)

--测试：
exec Mypage 2
id test
----------- ----------
11 j
12 k
13 l
14 m
15 n
16 o
17 p
18 q
19 r
20 s

(所影响的行数为 10 行)

--测试3

exec Mypage 3
id test
----------- ----------
21 t
22 u
23 v
24 w
25 x
26 y
27 z

(所影响的行数为 7 行)

--测试4

exec Mypage 4

--------------------------
对不起，输入的页码超过范围

(所影响的行数为 1 行)

--此为原创，转贴请注明出处，谢谢--

Answer 3

create procedure pro_fenye
@pageSize int,@pageNow int
as
begin
Select top @pageSize 字段名列表
from 表名
where id not in(
select top @pageSize*(@pageNow-1) id
from 表名)
end
--@pageSize 每页的显示多少条数据
--@pageNow 当前显示的是第几页