常见几种存储过程分页

本人今天学习存储过程分页功能，收集了以下几种存储过程分页功能：

第一种分页存储过程  适应SQL2005及以上

原地址：http://www.cnblogs.com/axinblog/articles/2399718.html

CREATE PROCEDURE [dbo].[GetRecordFromPage]     @SelectList       VARCHAR(2000),--欲选择字段列表    @TableSource      VARCHAR(100),--表名或视图表     @SearchCondition  VARCHAR(2000),--查询条件(where 单词已写)    @OrderExpression  VARCHAR(1000),--排序表达式(order by单词已写)    @PageIndex        INT = 1,--页号(第一页则@PageIndex=1,若@PageIndex<1则按第一页查询)    @PageSize         INT = 10--页尺寸(每页显示的信息条数)AS BEGIN    IF @SelectList IS NULL OR LTRIM(RTRIM(@SelectList)) = ''    BEGIN        SET @SelectList = '*'    END    PRINT @SelectList        SET @SearchCondition = ISNULL(@SearchCondition,'')    SET @SearchCondition = LTRIM(RTRIM(@SearchCondition))    IF @SearchCondition <> ''    BEGIN        IF UPPER(SUBSTRING(@SearchCondition,1,5)) <> 'WHERE'        BEGIN            SET @SearchCondition = 'WHERE ' + @SearchCondition        END    END    PRINT @SearchCondition    SET @OrderExpression = ISNULL(@OrderExpression,'')    SET @OrderExpression = LTRIM(RTRIM(@OrderExpression))    IF @OrderExpression <> ''    BEGIN        IF UPPER(SUBSTRING(@OrderExpression,1,5)) <> 'WHERE'        BEGIN            SET @OrderExpression = 'ORDER BY ' + @OrderExpression        END    END    PRINT @OrderExpression    IF @PageIndex IS NULL OR @PageIndex < 1    BEGIN        SET @PageIndex = 1    END    PRINT @PageIndex    IF @PageSize IS NULL OR @PageSize < 1    BEGIN        SET @PageSize = 10    END    PRINT  @PageSize    DECLARE @SqlQuery VARCHAR(4000)    SET @SqlQuery='SELECT '+@SelectList+',RowNumber     FROM         (SELECT ' + @SelectList + ',ROW_NUMBER() OVER( '+ @OrderExpression +') AS RowNumber           FROM '+@TableSource+' '+ @SearchCondition +') AS RowNumberTableSource     WHERE RowNumber BETWEEN ' + CAST(((@PageIndex - 1)* @PageSize+1) AS VARCHAR)     + ' AND ' +     CAST((@PageIndex * @PageSize) AS VARCHAR) --    ORDER BY ' + @OrderExpression    PRINT @SqlQuery    SET NOCOUNT ON    EXECUTE(@SqlQuery)    SET NOCOUNT OFF     RETURN @@RowCountEND

第二种方式

原地址：http://my.oschina.net/hellokitty/blog/63840

Create PROCEDURE [dbo].[GetRecordFromPage2005]  @fieldlist varchar(200) = '*',--字段名  @datasrc varchar(200),--表名  @filter varchar(200) = '',--过滤条件  @orderBy varchar(200),--排序  @pageNum int = 1,  @pageSize int = NULLAS  SET NOCOUNT ON  DECLARE     @STMT nvarchar(max),         -- SQL to executeSQL语句     @recct int                  -- total # of records (for GridView paging interface) 总记录条数--过滤条件  IF LTRIM(RTRIM(@filter)) = '' SET @filter = '1 = 1'  IF @pageSize IS NULL   BEGIN    SET @STMT =  N'SELECT   ' + @fieldlist +                  'FROM     ' + @datasrc +                 'WHERE    ' + @filter +                  'ORDER BY ' + @orderBy    EXEC (@STMT)                 -- return requested records   END   ELSE BEGIN    SET @STMT =  N'SELECT   @recct = COUNT(*)                  FROM     ' + @datasrc + '                  WHERE    ' + @filter    EXEC sp_executeSQL @STMT,   N'@recct INT OUTPUT', @recct = @recct OUTPUT    --SELECT @recct AS recct       -- return the total # of records-- 定义数据条数    DECLARE      @lbound int,      @ubound int    SET @pageNum = ABS(@pageNum)    SET @pageSize = ABS(@pageSize)    IF @pageNum < 1 SET @pageNum = 1    IF @pageSize < 1 SET @pageSize = 1    SET @lbound = ((@pageNum - 1) * @pageSize)    SET @ubound = @lbound + @pageSize + 1        IF @lbound >= @recct     BEGIN      SET @ubound = @recct + 1      SET @lbound = @ubound - (@pageSize + 1) -- return the last page of records if                                                                                             -- -- no records would be on the                                              -- specified page    END    SET @STMT =  N'SELECT  ' + @fieldlist + '     FROM    (                            SELECT  ROW_NUMBER() OVER(ORDER BY ' + @orderBy + ') AS row, *                            FROM    ' + @datasrc + '                            WHERE   ' + @filter + '                          ) AS tbl                  WHERE                          row > ' + CONVERT(varchar(9), @lbound) + ' AND                          row < ' + CONVERT(varchar(9), @ubound)    EXEC (@STMT)                 -- return requested records   END

第三种 适合SQL2000

原地址：http://www.51aspx.com/code/jQueryPager

CREATE  PROCEDURE [dbo].[ProcCustomPage](    @Table_Name               varchar(5000),          --表名    @Sign_Record              varchar(50),       --主键    @Filter_Condition         varchar(1000),         --筛选条件,不带where    @Page_Size                int,               --页大小    @Page_Index               int,          --页索引             @TaxisField               varchar(1000),            --排序字段    @Taxis_Sign               int,               --排序方式 1为 DESC, 0为 ASC            @Find_RecordList          varchar(1000),        --查找的字段    @Record_Count             int                --总记录数 ) ASBEGIN DECLARE  @Start_Number          intDECLARE  @End_Number            intDECLARE  @TopN_Number           int DECLARE  @sSQL                  varchar(8000)                 if(@Find_RecordList='')                 BEGIN                      SELECT @Find_RecordList='*'                 END SELECT @Start_Number =(@Page_Index-1) * @Page_SizeIF @Start_Number<=0 SElECT @Start_Number=0SELECT @End_Number=@Start_Number+@Page_SizeIF @End_Number>@Record_Count SELECT @End_Number=@Record_Count SELECT @TopN_Number=@End_Number-@Start_Number IF @TopN_Number<=0 SELECT @TopN_Number=0print @TopN_Number print @Start_Number print @End_Number print @Record_Count                 IF @TaxisField=''                 begin                    select  @TaxisField=@Sign_Record                 end IF @Taxis_Sign=0  BEGIN IF @Filter_Condition='' BEGIN SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'      WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'      WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'      ORDER BY '+@TaxisField+') order by '+@TaxisField+' DESC)order by '+@TaxisField ENDELSEBEGINSELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'      WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'      WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'      WHERE '+@Filter_Condition+' ORDER BY '+@TaxisField+') and '+@Filter_Condition+' order by '+@TaxisField+' DESC) and '+@Filter_Condition+' order by '+@TaxisField ENDENDELSEBEGINIF @Filter_Condition=''BEGINSELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'          WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'          WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'          ORDER BY '+@TaxisField+' DESC) order by '+@TaxisField+')order by '+@TaxisField+' DESC'     ENDELSEBEGINSELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'      WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'      WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'      WHERE '+@Filter_Condition+' ORDER BY '+@TaxisField+' DESC) and '+@Filter_Condition+' order by '+@TaxisField+') and '+@Filter_Condition+' order by '+@TaxisField+' DESC' ENDENDEXEC (@sSQL)IF @@ERROR<>0RETURN -3               RETURN 0 END  PRINT  @sSQL        GO