SQL2005分页存储过程

www.diy567.com是我弄的文娱网站,累了，去上面休息一下下

---------------------------------------------------------

-- 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

 

(26 行受影响)

*/