sql 分页脚本_存储过程

最新推荐文章于 2022-10-17 16:45:53 发布

慕容引刀

最新推荐文章于 2022-10-17 16:45:53 发布

阅读量463

点赞数 1

分类专栏：数据库文章标签： sqlserver

本文链接：https://blog.csdn.net/a1871837908/article/details/50763165

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

create Procedure [dbo].[uspPaging3]   
--declare
 @TableName varchar(500),               --表名,可多表联合  
 @Fields varchar(5000) = '*',           --字段名(全部字段为*)  
 @OrderBy varchar(5000),             --排序字段(必须!支持多字段)  
 @Filters varchar(5000) = '',          --条件语句(不用加where)  
 @pageSize int,                         --每页多少条记录  
 @pageIndex int = 1,                    --指定当前为第几页  
 @RecordCount int output-- 返回记录总数             

as  
begin

    SET NOCOUNT ON
    --防止读死锁
    SET TRANSACTION ISOLATION LEVEL Read Uncommitted
    -- transaction isolation level 

    Declare @sql nvarchar(4000);  
    --Declare @RecordCount int;      
    Declare @TotalPage int;  

    --计算总记录数  

    if ( @Filters ='' or @Filters = NULL)  
        set @sql = 'select @RecordCount = count(*) from ' + @TableName  
    else  
        set @sql = 'select @RecordCount = count(*) from ' + @TableName + ' where ' + @Filters  

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

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

    if (@Filters='' or @Filters=NULL)  
        set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderBy + ') as rowId,' + @Fields + ' from ' + @TableName   
    else  
        set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderBy + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @Filters      


    --处理页数超出范围情况  
    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 PageData where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' +  Convert(varchar(50),@EndRecord)  

    --select @sql  
    Exec(@Sql)      

end