存储过程分页实现代码，2005 T-SQL新增功能

今天看了看2005中T-SQL的新增功能，心血来潮，试了试利用CTE方法做的分页，感觉还不错。下面贴一下TopN方法和CTE方法的数据分页的存储过程，写的还算是比较通用。性能分析我以后会进一步研究一下。
代码还是先来一个测试用表，代码如下：

SET ANSI_NULLS ON
2

GO
3

SET QUOTED_IDENTIFIER ON
4

GO
5

CREATE TABLE [dbo].[testTable](
6

[id] [int] IDENTITY(1,1) NOT NULL,
7

[testDate] [datetime] NOT NULL CONSTRAINT [DF_testTable_testDate] DEFAULT (getdate()),
8

[name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
9

[description] [nchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
10

[orderColum] [float] NOT NULL,
11

CONSTRAINT [PK_testTable] PRIMARY KEY CLUSTERED
12

(
13

[id] ASC
14

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
15

) ON [PRIMARY]
16

填充一下表，加了3W条记录，倒是不多。

declare @i int
2

set @i = 1
3

while @i < 30001
4

begin
5

INSERT INTO testTable([name],[description],[orderColum])
6

VALUES('names', 'descriiption', @i * rand())
7

set @i = @i +1
8

end

1、TopN方法：这个方法就不多说了，相当普遍，页码少的时候比较好使。
[代码]

=============================================
2

-- Author: <Author,,microant>
3

-- Create date: <2007年7月5日,,>
4

-- Description: <Description,selectTopN分页方法,>
5

-- =============================================
6

CREATE PROCEDURE [dbo].[sp_SelectTopN](
7

-- Add the parameters for the stored procedure here
8

@TableName varchar(200) = 'testTable', --表名
9

@PageSize int = 15, --页面大小
10

@PageIndex int =2 , --页面的序号
11

--@IsCountNull bit =1, --返回记录是否为空
12

@IsAsc bit = 1 , --是否卫升序,升序为1，降序为0
13

@OderColumName varchar(200) = null, --排序字段名
14

@KeyID varchar(50) = 'id', --主键
15

@Conditions varchar(500) = null --查询条件
16

)
17

AS
18

set nocount on
19

declare @strSql nvarchar(1000)
21

declare @tempstr nvarchar(1000)
22

declare @orderstr nvarchar(400)
23

--判断排序方式,@IsAsc =1 升序, 0降序,设置排序语句
25

if @IsAsc = 1
26

begin
27

if(@OderColumName is null or @OderColumName = '')
28

set @orderstr = ' order by ' + @KeyID + ' asc'
29

else
30

set @orderstr = ' order by ' + @OderColumName + ' asc'
31

end
32

else
33

begin
34

if(@OderColumName is null or @OderColumName = '')
35

set @orderstr = ' order by ' + @KeyID + ' desc'
36

else
37

set @orderstr = ' order by ' + @OderColumName + ' desc'
38

end
39

--查询条件是否添加
41

if @Conditions is null
42

begin
43

set @tempstr = 'select top ' + str(@PageSize*@PageIndex) +' ' +@KeyID +' from ' + @TableName + @orderstr;
44

set @strSql = 'select top ' + str(@PageSize)+ ' * from ' + @TableName + ' where ' + @KeyID +' not in ( ' + @tempstr +')' + @orderstr;
45

end
46

else
47

begin
48

set @tempstr = 'select top ' + str(@PageSize*@PageIndex) +' ' +@KeyID +' from ' + @TableName+ ' where ' + @Conditions + ' ' + @orderstr;
49

set @strSql = 'select top ' + str(@PageSize)+ ' * from ' + @TableName + ' where ' + @Conditions + ' and ' + @KeyID +' not in (' + @tempstr +')' + @orderstr;
50

end
51

print @strSql
52

exec sp_executesql @strSql
53

set nocount off
54

[测试]

DECLARE @return_value int
2

EXEC @return_value = [dbo].[sp_SelectTopN]
3

@TableName = N'testTable',
4

@PageSize = 30,
5

@PageIndex = 4,
6

@IsAsc = 0,
7

@OderColumName = N'orderColum',
8

@KeyID = N'id',
9

@Conditions = 'id > 50'
10

SELECT 'Return Value' = @return_value
11

go
12

      2、CTE方法：
            CTE（常见表表达式）是一个可以由定义语句引用的临时命名的结果集，和临时表比较相似。一般形式如下，
            WITH <cte_alias>(<column_aliases>)
            AS
            (
              <cte_query>
            )
            SELECT *
            FROM <cte_alias>
            但是比临时表慢,但是考虑临时表会增大日志文件的大小,引起大量IO,CTE也就有他自己的优势。性能分析详情参见http://blog.csdn.net/yizhu2000/archive/2007/06/03/1636573.aspx

      [代码]

-- =============================================
2

-- Author: <Author,,microant>
3

-- Create date: <Create Date,,20070705>
4

-- Description: <Description,,CTE分页>
5

-- =============================================
6

CREATE PROCEDURE [dbo].[sp_CTE](
7

-- Add the parameters for the stored procedure here
8

@TableName varchar(200) = 'testTable', --表名
9

@PageSize int = 15, --页面大小
10

@PageIndex int =2 , --页面的序号
11

--@IsCountNull bit =1, --返回记录是否为空
12

@IsAsc bit = 1 , --是否卫升序,升序为1，降序为0
13

@OderColumName varchar(200) = null, --排序字段名
14

@KeyID varchar(50) = 'id', --主键
15

@Conditions varchar(500) = null --查询条件
16

)
17

AS
18

-- SET NOCOUNT ON added to prevent extra result sets from
20

-- interfering with SELECT statements.
21

SET NOCOUNT ON;
22

declare @strSql nvarchar(1000)
24

declare @tempstr nvarchar(1000)
25

declare @orderstr nvarchar(400)
26

declare @ctestr nvarchar(400)
27

--判断排序方式,@IsAsc =1 升序, 0降序,设置排序语句
29

if @IsAsc = 1
30

begin
31

if(@OderColumName is null or @OderColumName = '')
32

set @orderstr = ' order by ' + @KeyID + ' asc'
33

else
34

set @orderstr = ' order by ' + @OderColumName + ' asc'
35

end
36

else
37

begin
38

if(@OderColumName is null or @OderColumName = '')
39

set @orderstr = ' order by ' + @KeyID + ' desc'
40

else
41

set @orderstr = ' order by ' + @OderColumName + ' desc'
42

end
43

--CTE
45

set @ctestr ='with Table_CET
46

as
47

(
48

select
49

CEILING((ROW_NUMBER() OVER (' + @orderstr + '))/' + str(@PageSize) + ') as page_num, *
50

from ' + @TableName +
51

')' ;
52

set @strSql = @ctestr + 'select * from Table_CET where page_num = ' + str(@PageIndex)+ ' and ' + @Conditions;
55

print @strSql
57

begin
58

exec sp_executesql @strSql;
59

end