sql server分页存储过程三种实例

create procedure [dbo].[Pub_DataPager]
    @pTableName nvarchar(1000), --表名
    @pFieldNames nvarchar(max),--需要查询的字符串,以 ","分割,也可以是'*'
    @pWhere nvarchar(max),  --查询条件
    @pOrderby nvarchar(max),        --排序字符串,必需字段
    @pStartIndex int,
    @pEndIndex int,
    @pForceInnerJoin int=0 --1 不使用sql 引擎优化。0 使用 建议一般情况下不要使用该参数。
as
begin
    set nocount on
    declare @sql nvarchar(max)
    select @pWhere=rtrim(ltrim(isnull(@pWhere,''))),@pOrderby=rtrim(ltrim(isnull(@pOrderby, '')))
     
    if (@pOrderby='') set @pOrderby='IID'
    if (@pWhere <> '') set @pWhere = ' where ' + @pWhere
    set @pOrderby = ' order by ' + @pOrderby
    set @sql='select * from (select ' + @pFieldNames + ',convert(int,ROW_NUMBER() OVER (' + @pOrderBy + ')) as DataIndexNumber '
    set @sql=@sql + ' from ' + @pTableName + @pWhere + ') as a where DataIndexNumber between ' + convert(nvarchar(15),@pStartIndex) + ' and ' + convert(nvarchar(15),@pEndIndex)    
    set @sql=@sql + ' order by DataIndexNumber '
    if (@pForceInnerJoin=1)
    begin
        set @sql=@sql + ' option (force order)'
    end
    exec (@sql)
end

create Procedure [dbo].[Usp_Pager] 
 @tableName varchar(50),        --表名
 @fields varchar(5000) = '*',    --字段名(全部字段为*)
 @orderField varchar(5000),        --排序字段(必须!支持多字段)
 @sqlWhere varchar(5000) = Null,--条件语句(不用加where)
 @pageSize int,                    --每页多少条记录
 @pageIndex int = 1 ,            --指定当前为第几页
 @totalRecord int output            --返回总记录数 
as
begin
 
    Begin Tran --开始事务
 
    Declare @sql nvarchar(4000);
    Declare @TotalPage int;    
 
    --计算总记录数
          
    if (@SqlWhere='' or @sqlWhere=NULL)
        set @sql = 'select @totalRecord = count(*) from ' + @TableName
    else
        set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere
 
    EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数        
     
    --计算总页数
    select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
 
    if (@SqlWhere='' or @sqlWhere=NULL)
        set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName 
    else
        set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere    
         
     
    --处理页数超出范围情况
    if @PageIndex<=0 
        Set @pageIndex = 1
     
    if @pageIndex>@TotalPage
        Set @pageIndex = @TotalPage
 
     --处理开始点和结束点
    Declare @StartRecord int
    Declare @EndRecord int
     
    set @StartRecord = (@pageIndex-1)*@PageSize + 1
    set @EndRecord = @StartRecord + @pageSize - 1
 
    --继续合成sql语句
    set @Sql = @Sql + ') as ' + @TableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' +  Convert(varchar(50),@EndRecord)
     
    Exec(@Sql)
    ---------------------------------------------------
    If @@Error <> 0
      Begin
        RollBack Tran
        Return -1
      End
     Else
      Begin
        Commit Tran
        Return @totalRecord ---返回记录总数
      End   
end

/*  
  
    @CurrentPage为显示那一页,  
    @PageSize为每一页显示几行,  
    @Field_info为要显示的字段可以为*,  
    @Table_info为要查询的表或视图,  
    @Field_id 主键或唯一字段,  
    @Field_Order 排序字段,  
    @otherwhere为条件，不带“WHERE”,  
    @RecordCount为总行数, OUTPUT
    @PageCount为总页数, OUTPUT
    @SQLSTR 若发生错误可通过此参数输出SQL语句
  
*/  
ALTER PROCEDURE [dbo].[uoSp_RecordPager]  
    @CurrentPage int =1,
    @PageSize int = 10,  
    @Field_Info varchar(500),     
    @Table_info varchar(100),  
    @Field_id varchar(20),   
    @Field_Order varchar(100),   
    @otherwhere varchar(8000),   
    @RecordCount int output,   
    @PageCount int output,
    @SQLSTR varchar(8000) output  
AS  
   
begin  
    DECLARE @MinPage int, @MaxPage int 
    declare @sql varchar(8000)   
    declare @sqlt nvarchar(4000)   
   
    set @sqlt = 'SELECT @RecordCount = COUNT(' + @Field_id + ') FROM ' + @Table_Info
    IF @otherwhere != ''
            set @sqlt = @sqlt +' where '+@otherwhere   
   
    exec sp_executesql @sqlt,N'@RecordCount int output',@RecordCount output       
   
    --如何将exec执行结果放入变量中,如果是字符串就要用N,N后面的变量一定要和@sqlt里面的变量同名   
   
    IF @PageSize <= 0   
        begin  
            set @PageSize = 10   
        end  
   
--    else if @PageSize > @RecordCount   
--        begin   
--            set @pageSize = @RecordCount   
--        end   
   
    set @pagecount = @RecordCount / @PageSize   
   
    if ((@recordcount % @pagesize) != 0)                    --如果除不净则加一页   
        begin  
            set @PageCount = @RecordCount / @PageSize   
            set @PageCount = @pagecount + 1   
        end  
    else 
        begin  
            set @pagecount = @recordcount /@PageSize   
        end  
   
    IF @CurrentPage <= 0   
        begin  
            set @CurrentPage = 1   
        end  
   
    else if @CurrentPage > @pagecount   
        begin         
            set @currentpage = @pagecount       --如果输入页数大于总页数则符最后一页   
        end  
   
    SET @MinPage = (@CurrentPage - 1) * @PageSize + 1   
   
    SET @MaxPage = @MinPage + @PageSize - 1   
   
        
   
    BEGIN  
   
        if @Field_Info like '' 
            set @field_Info = '*' 
   
        IF @otherwhere like '' 
            set @sql = 'SELECT top '+str(@PageSize)+' * from
                             (SELECT ' +  @Field_Info + ' , row_number() over(order by ' + @Field_Order + ') as rownumber 
                                from ' + @Table_info + ' 
                             ) as TMP_TABLE where (rownumber between ' + convert(varchar(10),@minpage) + ' and ' + convert(varchar(10),@maxpage) + ') '
        ELSE 
           set @sql = 'SELECT top '+str(@PageSize)+' * from 
                            (SELECT ' +  @Field_Info + ' , row_number() over(order by ' + @Field_Order + ') as rownumber 
                                from ' + @Table_info + ' where 1=1 and '+ @otherwhere +'
                            ) as TMP_TABLE where (rownumber between ' + convert(varchar(10),@minpage) + ' and ' + convert(varchar(10),@maxpage) + ') and ' + @otherwhere   
   
        EXEC(@sql)   
        SET @SQLSTR = @sql
 
    END  
   
end