分页存储过程续 + 评论

按照saucer(思归)提供的网址
I decided to use the RowCount method wherever possible.


CREATE PROCEDURE Paging_RowCount
(
@Tables varchar(1000),
@PK varchar(100),
@Sort varchar(200) = NULL,
@PageNumber int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL)
AS

/*Default Sorting*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PK

/*Find the @PK type*/
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int

/*Set sorting variables.*/
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)

/*Default Page Number*/
IF @PageNumber < 1
SET @PageNumber = 1

/*Set paging variables.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS varchar(50))

/*Set filter & group variables.*/
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 = ''

/*Execute dynamic query*/
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 PROCEDURE dbo.TopicsOf
(
@pageIndexint = 1,
@pageSizeint = 20,
@titleidint,
@boardidint
)
AS

declare @Count int
declare @id int

--定位

set @Count = @pageSize * (@pageIndex -1) + 1
set rowcount @Count
select @id=[index] from topics where boardid = @boardid and titleid = @titleid order by [index]
--print @id


--返回记录
set rowcount @pageSize

select * from topics where [index] >= @id and boardid = @boardid and titleid = @titleid order by [index]

set rowcount 0

RETURN


引用jyk的帖子

这个过程好理解
原文地址信息
http://community.csdn.net/Expert/TopicView3.asp?id=4182510

翻动100万级的数据 —— 只需几十毫秒 之揭秘篇：有详细的说明，不要错过。
______________________________________________________

第一个存储过程,就是比大小那个,排序字段不可以有重复值,有重复值就比不出大小会得到错误的结果,但这种分页理论上速度最快

第二个和第三个存储过程粗看看好像是一种,也就是我现在最常用的,是正过来倒过来那种,重复值是没问题了,但联合查询,也就是多表查询时会有可能超时(这个我现在也没弄明白怎么回事)

我一般多表查询就用Not IN的方法,单表有重复就正过来反过来,对唯一字段就用第一种比大小的.还有一种游标的,那个效率太低,就8说了...