【求】存储过程分页代码【要求：速度快支持百万！可以支持任意字段排序，多表】

Create Procedure dbo.p_PageList

@tb varchar(150), --表名

@col varchar(50), --按该列来进行分页(必须是唯一性的字符，比如标识种子)

@colorder varchar(50), --需要排序的字段(为空，则默认为col)

@orderby bit, --排序,0-顺序,1-倒序

@collist varchar(800),--要查询出的字段列表,*表示全部字段

@countwhat varchar(500),--要统计的字段列表

@pagesize int, --每页记录数

@page int, --指定页

@condition varchar(1000),--查询条件

@pages int OUTPUT, --总页数

@countresult varchar(500) OUTPUT --统计结果

AS

SET NOCOUNT ON

DECLARE @coltemp varchar(50)

Declare @intResult Int

DECLARE @spos INT,@epos INT,@n INT

DECLARE @temp nvarchar(400)

DECLARE @r1 decimal(15,4),@r2 decimal(15,4),@r3 decimal(15,4),@r4 decimal(15,4),@r5 decimal(15,4),@r6 decimal(15,4),@r7 decimal(15,4),@r8 decimal(15,4),@r9 decimal(15,4),@r10 decimal(15,4)

Begin Tran

DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)

IF @colorder is null or rtrim(@colorder)='' 

set @colorder = @col

IF @condition is null or rtrim(@condition)=''

	BEGIN--没有查询条件 

	SET @where1=' Where ' 

	SET @where2=' '

	END

ELSE

	BEGIN--有查询条件 

	SET @where1=' Where ('+@condition+') AND '--本来有条件再加上此条件 

	SET @where2=' Where ('+@condition+') '--原本没有条件而加上此条件

	END

SET @temp=''

SET @n=1

IF LEN(@countwhat)>0

BEGIN

	IF RIGHT(@countwhat,1)<>',' SET @countwhat=@countwhat + ','

	SET @spos=1

	SET @epos=CHARINDEX(',', @countwhat, @spos)

	WHILE @epos>@spos

	BEGIN

		IF LEFT(SUBSTRING(@countwhat,@spos,@epos-@spos),3)='sum'

			SET @temp=@temp + ',@r' + CAST(@n AS nvarchar(5)) + '=isnull(' + SUBSTRING(@countwhat,@spos,@epos-@spos) + ',0.0)'

		ELSE

			SET @temp=@temp + ',@r' + CAST(@n AS nvarchar(5)) + '=' + SUBSTRING(@countwhat,@spos,@epos-@spos)

		SET @spos=@epos+1

		SET @epos=CHARINDEX(',', @countwhat, @spos)

		SET @n=@n+1

	END

END

WHILE @n<=10

BEGIN

	SET @temp=@temp + ',@r' + CAST(@n AS nvarchar(5)) + '=0'

	SET @n=@n+1

END



SET @sql='Select @r=COUNT(*)' + @temp + ' FROM '+@tb+@where2

print @sql

EXEC sp_executesql @sql,N'@r int OUTPUT,@r1 decimal(15,4) OUTPUT,@r2 decimal(15,4) OUTPUT,@r3 decimal(15,4) OUTPUT,@r4 decimal(15,4) OUTPUT,@r5 decimal(15,4) OUTPUT,@r6 decimal(15,4) OUTPUT,@r7 decimal(15,4) OUTPUT,@r8 decimal(15,4) OUTPUT,@r9 decimal(15,4) OUTPUT,@r10 decimal(15,4) OUTPUT',@intResult OUTPUT,@r1 OUTPUT,@r2 OUTPUT,@r3 OUTPUT,@r4 OUTPUT,@r5 OUTPUT,@r6 OUTPUT,@r7 OUTPUT,@r8 OUTPUT,@r9 OUTPUT,@r10 OUTPUT	--计算总记录数

Select @pages=CEILING((@intResult+0.0)/@pagesize)--计算总页数

SET @countresult=CAST(@r1 AS varchar) + ',' + CAST(@r2 AS varchar) + ',' + CAST(@r3 AS varchar) + ',' + CAST(@r4 AS varchar) + ',' + CAST(@r5 AS varchar) + ',' + CAST(@r6 AS varchar) + ',' + CAST(@r7 AS varchar) + ',' + CAST(@r8 AS varchar) + ',' + CAST(@r9 AS varchar) + ',' + CAST(@r10 AS varchar)

SET @spos=CHARINDEX('.', @col)

IF @spos>0 SET @coltemp='t.'+RIGHT(@col,LEN(@col)-@spos)

ELSE SET @coltemp=@col

IF @orderby=0 SET @sql='Select TOP '+CAST(@pagesize AS varchar)+' '+@collist+ ' FROM '+@tb+@where1+@col+'>(Select MAX('+@coltemp+') '+ ' FROM (Select TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+@col+' FROM '+@tb+@where2+'ORDER BY '+@colorder+') t) orDER BY '+@colorder

ELSE SET @sql='Select TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+@where1+@col+'<(Select MIN('+@coltemp+') '+ ' FROM (Select TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+@col+' FROM '+@tb+@where2+'ORDER BY '+@colorder+' DESC) t) orDER BY '+@colorder+' DESC'

IF @page=1 SET @sql='Select TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+@where2+'ORDER BY '+@colorder+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END

print @sql

EXEC(@sql)



If @@Error <> 0

	Begin

	RollBack Tran

	Return -1

	End

Else

	Begin

	Commit Tran

	Return @intResult

	End

SET NOCOUNT OFF

/*--用存储过程实现的分页程序

 显示指定表、视图、查询结果的第X页

 对于表中主键或标识列的情况,直接从原表取数查询，其它情况使用临时表的方法

 如果视图或查询结果中有主键,不推荐此方法



--邹建 2003.09(引用请保留此信息)--*/



/*--调用示例

 exec p_show '地区资料'



 exec p_show '地区资料',5,3,'地区编号,地区名称,助记码','地区编号'

--*/



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_show]') 

and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[p_show]

GO



CREATE Proc p_show

@QueryStr nvarchar(4000), --表名、视图名、查询语句

@PageSize int=10,   --每页的大小(行数)

@PageCurrent int=1,   --要显示的页

@FdShow nvarchar (4000)='',--要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段

@FdOrder nvarchar (1000)='' --排序字段列表

as

declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名

 ,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号

 ,@Obj_ID int    --对象ID

--表中有复合主键的处理

declare @strfd nvarchar(2000) --复合主键列表

 ,@strjoin nvarchar(4000) --连接字段

 ,@strwhere nvarchar(2000) --查询条件





select @Obj_ID=object_id(@QueryStr)

 ,@FdShow=case isnull(@FdShow,'') when '' then ' *' else ' '+@FdShow end

 ,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end

 ,@QueryStr=case when @Obj_ID is not null then ' '+@QueryStr else ' ('+@QueryStr+') a' end



--如果显示第一页，可以直接用top来完成

if @PageCurrent=1 

begin

 select @Id1=cast(@PageSize as varchar(20))

 exec('select top '+@Id1+@FdShow+' from '+@QueryStr+@FdOrder)

 return

end



--如果是表,则检查表中是否有标识更或主键

if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1

begin

 select @Id1=cast(@PageSize as varchar(20))

  ,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20))



 select @FdName=name from syscolumns where id=@Obj_ID and status=0x80

 if @@rowcount=0   --如果表中无标识列,则检查表中是否有主键

 begin

  if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK')

   goto lbusetemp  --如果表中无主键,则用临时表处理



  select @FdName=name from syscolumns where id=@Obj_ID and colid in(

   select colid from sysindexkeys where @Obj_ID=id and indid in(

    select indid from sysindexes where @Obj_ID=id and name in(

     select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID

   )))

  if @@rowcount>1  --检查表中的主键是否为复合主键

  begin

   select @strfd='',@strjoin='',@strwhere=''

   select @strfd=@strfd+',['+name+']'

    ,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']'

    ,@strwhere=@strwhere+' and b.['+name+'] is null'

    from syscolumns where id=@Obj_ID and colid in(

    select colid from sysindexkeys where @Obj_ID=id and indid in(

     select indid from sysindexes where @Obj_ID=id and name in(

      select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID

    )))

   select @strfd=substring(@strfd,2,2000)

    ,@strjoin=substring(@strjoin,5,4000)

    ,@strwhere=substring(@strwhere,5,4000)

   goto lbusepk

  end

 end

end

else

 goto lbusetemp



/*--使用标识列或主键为单一字段的处理方法--*/

lbuseidentity: 

 exec('select top '+@Id1+@FdShow+' from '+@QueryStr

  +' where '+@FdName+' not in(select top '

  +@Id2+' '+@FdName+' from '+@QueryStr+@FdOrder

  +')'+@FdOrder

  )

 return



/*--表中有复合主键的处理方法--*/

lbusepk:  

 exec('select '+@FdShow+' from(select top '+@Id1+' a.* from

  (select top 100 percent * from '+@QueryStr+@FdOrder+') a

  left join (select top '+@Id2+' '+@strfd+' 

  from '+@QueryStr+@FdOrder+') b on '+@strjoin+'

  where '+@strwhere+') a'

  )

 return



/*--用临时表处理的方法--*/

lbusetemp:  

select @FdName='[ID_'+cast(newid() as varchar(40))+']'

 ,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))

 ,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))



exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'

  into #tb from'+@QueryStr+@FdOrder+'

 select '+@FdShow+' from #tb where '+@FdName+' between '

 +@Id1+' and '+@Id2

 )

GO







本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fredrickhu/archive/2009/09/18/4566261.aspx

CREATE PROC p_page

@Tables   varchar(1000), --表名如testtable

@PrimaryKey   varchar(100),--表的主键,必须唯一性

@Sort   varchar(200)   =   NULL,--排序字段如f_Name asc或f_name desc(注意只能有一个排序字段)

@CurrentPage   int   =   1,--当前页

@PageSize   int   =   10,---每页大小

@Fields   varchar(1000)   =   '*',--显示的字段列表

@Filter   varchar(1000)   =   NULL,--条件语句,不加where,如 f_id>3

@Group   varchar(1000)   =   NULL,--分组字段

@TotalPage int    output --返回总页数





WITH ENCRYPTION 

AS



    SET NOCOUNT ON

    Declare @intResult Int    



    Begin Tran



    DECLARE @sql nvarchar(4000)

    

    if @Filter is null or @Filter=''  

        set @Sql = 'select @intResult = count(' + @PrimaryKey + ') from ' +  @Tables

        else

        set @Sql = 'select @intResult = count(' + @PrimaryKey + ') from ' +  @Tables + ' where ' + @Filter    

 

    EXEC sp_executesql @sql,N'@intResult int OUTPUT',@intResult OUTPUT--计算总记录数

    select @TotalPage=CEILING((@intResult+0.0)/@PageSize)--计算总页数       



    DECLARE   @strSortColumn   varchar(200)

    DECLARE   @operator   char(2)

    DECLARE   @SortType   char(10)

    DECLARE   @type   varchar(50)

    DECLARE   @prec   int



    IF   @Sort   IS   NULL   or   @Sort   =   ''

	Begin

	    SET   @SortType  = 'ASC'

	    SET   @operator   =   '>='

	    SET   @Sort   =   ltrim(rtrim(@PrimaryKey))

	End    	

    Else

	Set   @Sort = ltrim(rtrim(@Sort))



    SET @strSortColumn = @Sort 

	

    IF CHARINDEX('DESC',@Sort)>0

      BEGIN

        SET   @strSortColumn   =   SUBSTRING(@Sort, 1, len(@Sort)-4) --REPLACE(@Sort,   'DESC',   '')

        SET   @SortType   =   SUBSTRING(@Sort, len(@Sort)-4,len(@Sort))

        SET   @operator   =   '<='

      END

   

   IF CHARINDEX('ASC',   @Sort)   >   0

      BEGIN  

        SET   @strSortColumn   =   SUBSTRING(@Sort, 1, len(@Sort)-3) --REPLACE(@Sort,   'ASC',   '')

        SET   @SortType   =   SUBSTRING(@Sort, len(@Sort)-3,len(@Sort))

        SET   @operator   =   '>='

      END

    

    Select   @type=t.name,   @prec=c.prec

    FROM   sysobjects   o   

    JOIN   syscolumns   c   on   o.id=c.id

    JOIN   systypes   t   on   c.xusertype=t.xusertype

    Where   o.name   =   @Tables   AND   c.name   =   @strSortColumn

    

    IF   CHARINDEX('char',   @type)   >   0

          SET   @type   =   rtrim(@type)   +   '('   +   CAST(@prec   AS   varchar)   +   ')'

    

    IF CHARINDEX('DESC',@Sort)>0 or CHARINDEX('ASC',   @Sort)  >  0

        SET   @strSortColumn   =   '[' + ltrim(Rtrim(@strSortColumn)) +']'



    SET   @Sort = @strSortColumn + ' ' + @SortType



    DECLARE   @strPageSize   varchar(50)

    DECLARE   @strStartRow   varchar(50)

    DECLARE   @strFilter   varchar(1000)

    DECLARE   @strSimpleFilter   varchar(1000)

    DECLARE   @strGroup   varchar(1000)

    

    IF @CurrentPage < 1

	SET @CurrentPage = 1

    

    SET @strPageSize = CAST(@PageSize   AS   varchar(50))

    SET @strStartRow = CAST(((@CurrentPage   -   1)*@PageSize   +   1)   AS   varchar(50))

    

    IF   @Filter   IS   NOT   NULL   AND   @Filter   !=   ''

      BEGIN

        SET   @strFilter   =   '   Where   '   +   @Filter   +   '   '

        SET   @strSimpleFilter   =   '   AND   '   +   @Filter   +   '   '

      END

    ELSE

      BEGIN

        SET   @strSimpleFilter   =   ''

        SET   @strFilter   =   ''

      END

    

    IF (@Group IS NOT NULL) AND (@Group !=   '')

      SET   @strGroup   =   ' GROUP BY '   +   @Group   +   ' '

    ELSE

      SET   @strGroup   =   ''

    

if @CurrentPage =1

  begin

    set @sql = 'SELECT TOP ' + ltrim(rtrim(str(@PageSize))) + ' ' +   @Fields + ' FROM ' + @Tables + ' ' + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort

  end

else

  begin

    set @sql = 'DECLARE @SortColumn '   +   @type   +   ' SET ROWCOUNT '  +   @strStartRow + ' '  

	     + 'Select @SortColumn='   +   @strSortColumn   +   ' FROM '  +   @Tables   

	     +  @strFilter   +   ' '   +   @strGroup   +   ' ORDER BY '   +   @Sort   

	     + 'SET ROWCOUNT '  +   @strPageSize   +   ' Select '   +   @Fields   

	     + ' FROM '   +   @Tables   +   ' Where '   +   @strSortColumn   

	     + @operator   +   ' @SortColumn '   +   @strSimpleFilter   +   ' '   

	     + @strGroup   +   ' ORDER BY '   +   @Sort

  end

  

    exec(@sql)



    If @@Error <> 0

       Begin

           RollBack Tran

           Return -1

       End

    Else

       Begin

           Commit Tran

           Return @intResult ---返回记录总数

       End

GO

CREATE PROCEDURE [dbo].[Pg_Paging1]

(

 @TableName varchar(50),            --表名

 @ReFieldsStr varchar(500) = '*',   --字段名(全部字段为*)

 @OrderString varchar(200),         --排序字段支持多字段不用加order by)((必须要有该项)

 @WhereString varchar(MAX) =N'',    --条件语句(不用加where)

 @PageSize int,                     --每页多少条记录

 @PageIndex int = 1 ,               --指定当前为第几页

 @TotalRecord int output,           --返回总记录数

 @SearchTime decimal(18,4) output   --返回用时

)

AS

BEGIN    



    --处理开始点和结束点

    Declare @StartRecord int;

    Declare @EndRecord int; 

    Declare @TotalCountSql nvarchar(500); 

    Declare @SqlString nvarchar(MAX);    

    set @StartRecord = (@PageIndex-1)*@PageSize + 1;

    set @EndRecord = @StartRecord + @PageSize - 1 ;

    SET @TotalCountSql= N'select @TotalRecord = count(*) from ' + @TableName;--总记录数语句

    SET @SqlString = N'(select row_number() over (order by '+ @OrderString +') as rowId,'+@ReFieldsStr+' from '+ @TableName;--查询语句

    --

    IF (@WhereString! = '' or @WhereString!=null)

        BEGIN

            SET @TotalCountSql=@TotalCountSql + ' with(nolock) where '+ @WhereString;

            SET @SqlString =@SqlString+ ' with(nolock) where '+ @WhereString;            

        END



	declare   @d   datetime   

	set   @d=getdate() 

    --第一次执行得到

    --IF(@TotalRecord is null)

    --   BEGIN

           EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;--返回总记录数

    --  END

    ----执行主语句

    set @SqlString ='select * from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' +  ltrim(str(@EndRecord));

    Exec(@SqlString) 



	set @SearchTime = datediff(ms,@d,getdate())  

 

END