最实简单的分页的存储过程

方便高效分页存储过程（感谢各位能提出宝贵的意见)

使用最简单方便，功能强大的分页存储过程。免去繁多的参数，再不用将SQL语句拆分成若干个参数了
调用只需要运行:NewPageView exec 查询字符串,当前页,显示条数

即可,查询字符串可使用任意没有语法错误的SQL语句，包括 order by

主意需要将SQL语句中的单引号用俩个单引号或^（shift+6）代替

例如，支持任意列索引，或无索引分页

exec NewPageView'select b,c,d,e,f from temp where b=^测试^',10000,20
exec NewPageView'select b,c,d,e,f from temp where b=’’测试’’',10000,20

查询结果为俩个表，表1为当前页内容，表2为记录数据，总计页数等信息

也许细心的网友发现该存储过程的核心是OW_NUMBER() OVER()，认为效率很低，其实在实际测试中效率非常的高，若在没有Order By的情况下会以 1作为索引列速度更快，在400万条6列vachar(50)的数据表中分页查询每页显示50条数据最慢用时1秒，最快0秒，在有ORDER BY的情况下最慢需要7秒,最快0秒，关键是使用方便

感谢大家支持，欢迎大家提出宝贵的意见。

 

/****** Object:  StoredProcedure [dbo].[NewPageView]  Script Date: 12/27/2012 12:24:12 ******/

/*创建日期：2007年，更新日期2012.12.27，创建人：雨 QQ：312430633*/

 

SET ANSI_NULLSON

GO

SET QUOTED_IDENTIFIERON

GO

 ALTERPROCEDURE [dbo].[NewPageView]

@tsql varchar(4000),----*****SQL语句

@pageindex int,    ----*****页码

@PageSize int     ----*****每页显示条数

 as

 Declare@SqlSelect varchar(4000)

Declare @UnSqlSelect  varchar(4000)

Declare @orderby  varchar(4000)

Declare @AllowPagingSql  varchar(4000)

 

---判断是否排序

if CHARINDEX('order by',@tsql)<> 0

begin

    set @SqlSelect=replace(substring(@tsql,1,CHARINDEX('order by',@tsql)-1),'$','''') 

    set @UnSqlSelect= replace(@SqlSelect,'Select','')

    set @orderby=replace(substring(@tsql,CHARINDEX('order by',@tsql),len(@tsql)),'$','''')     

    set @AllowPagingSql=

       'select * from (SELECT  ROW_NUMBER() OVER('+@orderby+') AS AllowPagingId,* FROM ( select top '+convert(varchar(10),@pageindex*@PageSize)+' '

       +@UnSqlSelect

       +' ) as  tbs1 ) as table2 where AllowPagingId between '

       +convert(varchar(10),((@pageindex-1)*@PageSize+1))+' and '

       +convert(varchar(10),@pageindex*@PageSize)       

    exec  (@AllowPagingSql) 

end

else

begin

    set @SqlSelect=replace(@tsql,'$','''') 

    set @orderby=''

    set @AllowPagingSql=

       'select *  from (SELECT ROW_NUMBER() OVER(ORDER BY orderbyID DESC) AS AllowPagingId ,* FROM  ( select *, 1 as orderbyID from ( select top '+convert(varchar(10),@pageindex*@PageSize)+' '

       +@UnSqlSelect

       +' ) as  tbs1 )   as Tabl1 ) as table2 where AllowPagingId between '

       +convert(varchar(10),((@pageindex-1)*@PageSize+1))+' and '

       +convert(varchar(10),@pageindex*@PageSize)       

       exec  (@AllowPagingSql)

end

set @AllowPagingSql='select

case

     when count(*)%'+convert(varchar(10),@PageSize)+'=0 then count(*)/'+convert(varchar(10),@PageSize)+'

     when count(*)%'+convert(varchar(10),@PageSize)+'<>0 then count(*)/'+convert(varchar(10),@PageSize)+'+1

end as pageCount,count(*) as RowsCount from ('+@SqlSelect+') as tab1'

 exec  (@AllowPagingSql)

 使用示例:

exec NewPageView  SQL语句,页码,每页显示条数

1、  exec NewPageView'select b,c,d,e,f from temp',10000,20

2、  exec NewPageView'select b,c,d,e,f from temp where b=^测试^',10000,20
exec NewPageView'select b,c,d,e,f from temp where b=’’测试’’',10000,20

提示：（示例2中的^替换查询语句中的单引号’，也可以使用’’俩个单引号替换单引号。）
        因为SQL语句会在存储过程中当作字符串进行处理）

3、  exec NewPageView'select b,c,d,e,f from temp order by b',10000,20

 

 

 

 

 

 

200万条带Order BY用时

400万条带Order BY用时

 

在这里在为大家增上一段C#的分页数据生成类

 

///<summary>

    ///数ºy据Y分¤?页°3

    ///</summary>

    ///<param name="Sql">查¨¦询¡¥语®?句?</param>

    ///<param name="PageIndex">页°3码?</param>

    ///<param name="PageSize">每?页°3显?示º?条¬?数ºy</param>

    ///<returns></returns>

    public static string PagingAll(string Sql,int PageIndex,int PageSize)

    {

        string selectStr = "", orderStr = "";

        int rows = PageIndex * PageSize;       

        string val = "";

 

        selectStr = Sql.ToUpper();

        if (selectStr.IndexOf("TOP") == -1)

        {

            selectStr = "SELECT TOP "+ rows.ToString()+" " + selectStr.Substring(selectStr.IndexOf("SELECT"), selectStr.Length - selectStr.IndexOf("SELECT"));

        }

        if (selectStr.IndexOf("ORDER BY") != -1)

        {

            orderStr = selectStr.Substring(selectStr.IndexOf("ORDER BY"), selectStr.Length - selectStr.IndexOf("ORDER BY"));

            selectStr = selectStr.Substring(0, selectStr.IndexOf("ORDER BY"));           

        }

 

        if (selectStr.IndexOf("ORDER BY") == -1)

        {

            val = @"select *  from (SELECT  ROW_NUMBER() OVER(ORDER BY orderbyID DESC) AS AllowPagingId

                ,* FROM  ( select *, 1 as orderbyID from ( " + selectStr +@" ) as  tbs1 )  

                as Tabl1 ) as table2 where

                AllowPagingId between " + ((PageIndex - 1) * PageSize).ToString() +" and " + rows.ToString();

        }

        else

        {

 

            val = @"select *  from (SELECT  ROW_NUMBER() OVER(" + orderStr + @") AS AllowPagingId

                ,* FROM  ( select * from ( " + selectStr +@" ) as  tbs1 )  

                as Tabl1 ) as table2 where

                AllowPagingId between " + ((PageIndex - 1) * PageSize).ToString() +" and " + rows.ToString();           

        }

        return val;

    }

 

C#使用示例

TextBox1.Text = PagingAll("select * from temp", 5, 10);