--要分頁的原始數據
CREATE TABLE tb(
ID    int PRIMARY KEY,  --記錄編號
grade  varchar(10),     --類別名稱
uptime datetime)        --更新時間
INSERT tb SELECT 1 ,'a','2004-12-11'
UNION ALL SELECT 2 ,'b','2004-12-11'
UNION ALL SELECT 3 ,'c','2004-12-11'
UNION ALL SELECT 4 ,'a','2004-12-12'
UNION ALL SELECT 5 ,'c','2004-12-13'
UNION ALL SELECT 6 ,'c','2004-12-13'
UNION ALL SELECT 7 ,'a','2004-12-14'
UNION ALL SELECT 8 ,'a','2004-12-15'
UNION ALL SELECT 9 ,'b','2004-12-16'
UNION ALL SELECT 10,'b','2004-12-17'
UNION ALL SELECT 11,'a','2004-12-17'

--分頁定義表
CREATE TABLE tb_Page(
grade   varchar(10) PRIMARY KEY, --類別名稱,與tb表的grade關聯
Records int,                     --每頁顯示的記錄數
Orders  int)                     --在頁中的顯示順序
INSERT tb_Page SELECT 'c',2,1
UNION  ALL     SELECT 'b',1,2
UNION  ALL     SELECT 'a',2,3
GO

--實現分頁處理的存儲過程
CREATE PROC p_PageView
@PageCurrent int=1  --要顯示的當前頁碼
AS
SET NOCOUNT ON
--得到每頁的記錄數
DECLARE @PageSize int
SELECT @PageSize=SUM(Records) FROM tb_Page
IF ISNULL(@PageSize,0)<0 RETURN

--分頁顯示處理
SET @PageCurrent=@PageCurrent*@PageSize
SET ROWCOUNT @PageCurrent
SELECT SID=IDENTITY(int,1,1),ID 
INTO # FROM(
	SELECT TOP 100 PERCENT a.ID 
	FROM tb a
		LEFT JOIN tb_Page b ON a.grade=b.grade
	ORDER BY CASE WHEN b.grade IS NULL THEN 1 ELSE 0 END,--分類沒有定義的顯示在最后
		((SELECT COUNT(*) FROM tb 
			WHERE grade=a.grade 
				AND (uptime>a.uptime OR uptime=a.uptime AND id>=a.id))-1)
		/b.Records,
		b.Orders,a.ID DESC)a
IF @PageCurrent>@PageSize
BEGIN
	SET @PageCurrent=@PageCurrent-@PageSize
	SET ROWCOUNT @PageCurrent
	DELETE FROM #
END
SELECT a.* FROM tb a,# b
WHERE a.ID=b.ID
ORDER BY b.SID
GO

--調用
EXEC p_PageView 2
/*--結果
ID          grade      uptime                                                 
----------- ---------- ------------------------------------------------------ 
3           c          2004-12-11 00:00:00.000
9           b          2004-12-16 00:00:00.000
7           a          2004-12-14 00:00:00.000
4           a          2004-12-12 00:00:00.000
2           b          2004-12-11 00:00:00.000
--*/