SQL2005分页存储过程

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

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

转载于:https://www.cnblogs.com/mylife_001/archive/2011/03/30/1999451.html