随笔- 12  评论- 58  文章- 0 

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

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

 1SET ANSI_NULLS ON
 2GO
 3SET QUOTED_IDENTIFIER ON
 4GO
 5CREATE TABLE [dbo].[testTable](
 6    [id] [int] IDENTITY(1,1NOT 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 = OFFON [PRIMARY]
15ON [PRIMARY]
16

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

1declare @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方法:这个方法就不多说了,相当普遍,页码少的时候比较好使。
         [代码]    

 1=============================================
 2-- Author:        <Author,,microant>
 3-- Create date: <2007年7月5日,,>
 4-- Description:    <Description,selectTopN分页方法,>
 5-- =============================================
 6CREATE 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)
17AS
18    set nocount on
19    
20    declare @strSql nvarchar(1000)
21    declare @tempstr nvarchar(1000)
22    declare @orderstr nvarchar(400)
23    
24--判断排序方式,@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
40--查询条件是否添加
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
53set nocount off
54

         [测试]

 1DECLARE    @return_value int
 2EXEC    @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'
10SELECT    'Return Value' = @return_value
11go
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
   
      [代码]

 1-- =============================================
 2-- Author:        <Author,,microant>
 3-- Create date: <Create Date,,20070705>
 4-- Description:    <Description,,CTE分页>
 5-- =============================================
 6CREATE 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)
17AS
18
19    -- SET NOCOUNT ON added to prevent extra result sets from
20    -- interfering with SELECT statements.
21    SET NOCOUNT ON;
22
23    declare @strSql nvarchar(1000)
24    declare @tempstr nvarchar(1000)
25    declare @orderstr nvarchar(400)
26    declare @ctestr nvarchar(400)
27    
28    --判断排序方式,@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    
44    --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    
53    
54    set @strSql = @ctestr + 'select * from Table_CET where page_num = ' + str(@PageIndex)+ ' and ' + @Conditions;
55    
56    print @strSql
57    begin
58    exec sp_executesql @strSql;
59    end

   [测试]

 1DECLARE    @return_value int
 2EXEC    @return_value = [dbo].[sp_CTE]
 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'
10SELECT    'Return Value' = @return_value
11GO

      3、以前没太关注SQL server 2005的新功能,一直在用MySQL,或者2000,每天关注Oracle但是很不幸都没机会做过什么。不曾想加了不少共能,得好好看看。师兄再给我说他们在用Informax等一系列工具做数据仓库,很高兴听到几个不懂得名词,很不幸没有机会见识见识。还是务实点看看2005提供了啥吧。
            T-SQL也出现了不少新功,CTE就不错看着,比较好使,具体参见https://www.microsoft.com/china/msdn/library/data/sqlserver/05TSQLEnhance.mspx?mfr=true

posted on 2007-07-05 19:53  microant  阅读(...)  评论(... 编辑 收藏