SQL Server分页存储过程 

  1 CREATE PROCEDURE [dbo].[Pro_GetPageData]
  2 
  3 (
  4 
  5     @TableName        nvarchar(3000),            -- 表名
  6 
  7     @ReturnFields    nvarchar(3000) = '*',    -- 需要返回的列 
  8 
  9     @PageSize        int = 10,                -- 每页记录数
 10 
 11     @PageIndex        int = 0,                -- 当前页码
 12 
 13     @Where            nvarchar(3000) = '',        -- 查询条件
 14 
 15     @OrderBy        nvarchar(200),            -- 排序字段名 最好为唯一主键
 16 
 17     @OrderType        int = 1                    -- 排序类型 1:降序 其它为升序
 18 
 19 )
 20 
 21 AS
 22 
 23     DECLARE @TotalRecord int
 24 
 25     DECLARE @TotalPage int
 26 
 27     DECLARE @CurrentPageSize int
 28 
 29     DECLARE @TotalRecordForPageIndex int
 30 
 31     declare @CountSql nvarchar(4000)  
 32 
 33     
 34 
 35     if @OrderType = 1
 36 
 37         BEGIN
 38 
 39             set @OrderBy = ' Order by ' + REPLACE(@OrderBy,',',' desc,') + ' desc '
 40 
 41         END
 42 
 43     else
 44 
 45         BEGIN
 46 
 47             set @OrderBy = ' Order by ' + REPLACE(@OrderBy,',',' asc,') + ' asc '        
 48 
 49         END
 50 
 51     
 52 
 53     -- 总记录
 54 
 55     set @CountSql='SELECT @TotalRecord=Count(*) From '+@TableName+' '+@Where
 56 
 57     execute sp_executesql @CountSql,N'@TotalRecord int out',@TotalRecord out
 58 
 59     
 60 
 61     SET @TotalPage=(@TotalRecord-1)/@PageSize+1
 62 
 63     
 64 
 65     -- 查询页数不得大于总页数
 66 
 67     if(@PageIndex > @TotalPage)
 68 
 69         set @PageIndex = @TotalPage
 70 
 71     
 72 
 73     SET @CurrentPageSize=(@PageIndex-1)*@PageSize
 74 
 75 
 76 
 77         
 78 
 79     -- 返回记录
 80 
 81     set @TotalRecordForPageIndex=@PageIndex*@PageSize
 82 
 83     
 84 
 85     exec    ('SELECT *
 86 
 87             FROM (SELECT TOP '+@TotalRecordForPageIndex+' '+@ReturnFields+', ROW_NUMBER() OVER ('+@OrderBy+') AS ROWNUM
 88 
 89             FROM '+@TableName+ ' ' + @Where +' ) AS TempTable
 90 
 91             WHERE TempTable.ROWNUM > 
 92 
 93             '+@CurrentPageSize)
 94 
 95 
 96 
 97     
 98 
 99     -- 返回总页数和总记录
100 
101     SELECT @TotalPage as PageCount,@TotalRecord as RecordCount

 

posted @ 2018-10-10 13:45  小鑫121  阅读(2015)  评论(0)    收藏  举报