---------------------------------------------------------
-- Author : SQL2005分页存储过程htl258(Tony)
-- Date : 2009-09-22 13:00:50
-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
-- Mar 29 2009 10:27:29
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
---------------------------------------------------------
--创建测试表
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([Col] NVARCHAR(10))
--填充数据
INSERT tb
SELECT TOP 26 col1=CHAR(64+ROW_NUMBER()OVER(ORDER BY GETDATE()))
FROM sys.objects
--创建分页的存储过程
IF OBJECT_ID('[sp_page]') IS NOT NULL
DROP PROC [sp_page]
GO
CREATE PROC sp_page
@tablename NVARCHAR(50),--表名
@pagerow INT=0,--每页显示的行数(0为全部)
@pagenow INT=1, --要显示第几页(默认为)
@ordercol VARCHAR(50)=NULL, --排序字段
@order BIT=0 --排序规则0顺序,1倒序
AS
DECLARE @s NVARCHAR(MAX)
SET @s='
WITH t AS
(
SELECT
rownum=ROW_NUMBER()OVER(ORDER BY '+ISNULL(@ordercol,'GETDATE()')+
CASE @order WHEN 1 THEN ' desc' ELSE ' asc' END+'),
*
FROM '+@tablename +'
)
SELECT *
FROM t'
IF @pagerow>0
SET @s=@s+'
WHERE rownum BETWEEN '+LTRIM(@pagerow*(@pagenow-1)+1)+'
AND '+LTRIM(@pagerow*@pagenow)
EXEC(@s)
GO
--返回tb表每页行第页的记录
EXEC sp_page 'tb',6,2
/*
rownum Col
-------------------- ----------
7 G
8 H
9 I
10 J
11 K
12 L
(6 行受影响)
*/
EXEC sp_page 'tb',6,2,'col',2
/*
rownum Col
-------------------- ----------
7 T
8 S
9 R
10 Q
11 P
12 O
(6 行受影响)
*/
--返回所有记录
EXEC sp_page 'tb'
/*
rownum Col
-------------------- ----------
1 A
2 B
3 C
4 D
5 E
6 F
7 G
8 H
9 I
10 J
11 K
12 L
13 M
14 N
15 O
16 P
17 Q
18 R
19 S
20 T
21 U
22 V
23 W
24 X
25 Y
26 Z