• 【干货】SqlServer 总结几种存储过程分页的使用


    就我而言写代码最烦的就是处理数据,其中之一就是分页的使用。

    有的代码写多了,总结出一套适用自己的分页方法;有的查一下资料借鉴一下套用起来也达到目的。

    那么小编在这里给大家总结几个方法供大家做一下参考。

    分页方法一:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO/*********************************************************  
    * 作    用:数据分页
    * 作    者:Ozawa
    * 作者博客:https://www.cnblogs.com/xiongze520/
    * 创建日期:2019-05-13
    * 使用说明:
        --调用例子:
        --1.单表/单排序
        EXEC proc_DataPagination @TableNames='bigtable',@PrimaryKey='d_id',@Fields='d_id,d_title,d_content,d_time',@PageSize=20,@CurrentPage=1,@Filter ='',@Group='',@Order='d_id desc'
        --2.单表/多排序
        EXEC proc_DataPagination 'bigtable','d_id','*',20,0,'','','d_time asc,d_id desc'
        --3.多表/单排序
        EXEC proc_DataPagination 'bigtable left join bigtable_author on bigtable.d_id=bigtable_author.BigTable_id', 'bigtable.d_id', 'bigtable.d_id,bigtable.d_title,bigtable.d_content,bigtable.d_time,bigtable_author.d_author', 20, 0, '', '', 'bigtable.d_id asc'
        --4.多表/多排序
        EXEC proc_DataPagination 'bigtable left join bigtable_author on bigtable.d_id=bigtable_author.BigTable_id', 'bigtable.d_id', 'bigtable.d_id,bigtable.d_title,bigtable.d_content,bigtable.d_time,bigtable_author.d_author', 20, 0, '', '', 'bigtable.d_time asc,bigtable.d_id desc'
    
    *********************************************************/  
     CREATE PROCEDURE [dbo].[proc_DataPagination]  
    @TableNames VARCHAR(200),    --表名,可以是多个表,但不能用别名
    @PrimaryKey VARCHAR(100),    --主键,可以为空,但@Order为空时该值不能为空
    @Fields    VARCHAR(200),        --要取出的字段,可以是多个表的字段,可以为空,为空表示select *
    @PageSize INT,            --每页记录数
    @CurrentPage INT,        --当前页,0表示第1页
    @Filter VARCHAR(200) = '',    --条件,可以为空,不用填 where
    @Group VARCHAR(200) = '',    --分组依据,可以为空,不用填 group by
    @Order VARCHAR(200) = ''    --排序,可以为空,为空默认按主键升序排列,不用填 order by
    AS
    BEGIN
        DECLARE @SortColumn VARCHAR(200)
        DECLARE @Operator CHAR(2)
        DECLARE @SortTable VARCHAR(200)
        DECLARE @SortName VARCHAR(200)
        IF @Fields = ''
            SET @Fields = '*'
        IF @Filter = ''
            SET @Filter = 'WHERE 1=1'
        ELSE
            SET @Filter = 'WHERE ' +  @Filter
        IF @Group <>''
            SET @Group = 'GROUP BY ' + @Group
    
        IF @Order <> ''
        BEGIN
            DECLARE @pos1 INT, @pos2 INT
            SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC')
            IF CHARINDEX(' DESC', @Order) > 0
                IF CHARINDEX(' ASC', @Order) > 0
                BEGIN
                    IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order)
                        SET @Operator = '<='
                    ELSE
                        SET @Operator = '>='
                END
                ELSE
                    SET @Operator = '<='
            ELSE
                SET @Operator = '>='
            SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '')
            SET @pos1 = CHARINDEX(',', @SortColumn)
            IF @pos1 > 0
                SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1)
            SET @pos2 = CHARINDEX('.', @SortColumn)
            IF @pos2 > 0
            BEGIN
                SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1)
                IF @pos1 > 0 
                    SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1)
                ELSE
                    SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2)
            END
            ELSE
            BEGIN
                SET @SortTable = @TableNames
                SET @SortName = @SortColumn
            END
        END
        ELSE
        BEGIN
            SET @SortColumn = @PrimaryKey
            SET @SortTable = @TableNames
            SET @SortName = @SortColumn
            SET @Order = @SortColumn
            SET @Operator = '>='
        END
    
        DECLARE @type varchar(50)
        DECLARE @prec int
        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 = @SortTable AND c.name = @SortName
        IF CHARINDEX('char', @type) > 0
        SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
    
        DECLARE @TopRows INT
        SET @TopRows = @PageSize * @CurrentPage + 1
        print @TopRows
        print @Operator
        EXEC('
            DECLARE @SortColumnBegin ' + @type + '
            SET ROWCOUNT ' + @TopRows + '
            SELECT @SortColumnBegin=' + @SortColumn + ' FROM  ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + '
            SET ROWCOUNT ' + @PageSize + '
            SELECT ' + @Fields + ' FROM  ' + @TableNames + ' ' + @Filter  + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + '    
        ')    
    END
    
    GO

    方法二:

    /*********************************************************  
    * 作    用:数据分页
    * 作    者:Ozawa
    * 作者博客:https://www.cnblogs.com/xiongze520/
    * 创建日期:2019-05-13
    * 使用说明:
          declare @pageCount int 
          exec commonPagination  'job_id,job_desc','jobs','job_id', 'asc','1=1',2,2,@pageCount output 
          --select '总页数为:' + str(@pageCount)
    *********************************************************/

    CREATE PROCEDURE commonPagination @columns varchar(
    500), --要显示的列名,用逗号隔开 @tableName varchar(100), --要查询的表名 @orderColumnName varchar(100), --排序的列名 @order varchar(50), --排序的方式,升序为asc,降序为 desc @where varchar(100), --where 条件,如果不带查询条件,请用 1=1 @pageIndex int, --当前页索引 @pageSize int, --页大小(每页显示的记录条数) @pageCount int --总页数,输出参数 as begin declare @sqlRecordCount nvarchar(1000) --得到总记录条数的语句 declare @sqlSelect nvarchar(1000) --查询语句 set @sqlRecordCount=N'select @recordCount=count(*) from ' +@tableName + ' where '+ @where declare @recordCount int --保存总记录条数的变量 exec sp_executesql @sqlRecordCount,N'@recordCount int output',@recordCount output --动态 sql 传参 if( @recordCount % @pageSize = 0) --如果总记录条数可以被页大小整除 set @pageCount = @recordCount / @pageSize --总页数就等于总记录条数除以页大小 else --如果总记录条数不能被页大小整除 set @pageCount = @recordCount / @pageSize + 1 --总页数就等于总记录条数除以页大小加1 set @sqlSelect = N'select '+@columns+' from ( select row_number() over (order by ' +@orderColumnName+' '+@order +') as tempid,* from ' +@tableName+' where '+ @where +') as tempTableName where tempid between ' +str((@pageIndex - 1)*@pageSize + 1 ) +' and '+str( @pageIndex * @pageSize) exec (@sqlSelect) --执行动态Sql end

    方法三:

    create PROCEDURE commonPagination
    (@TableName   VARCHAR(2000),         --表名
     @ReFieldsStr VARCHAR(1000) = '*',     --字段名(全部字段为*)
     @OrderString VARCHAR(200),         --排序字段(必须!支持多字段不用加order by)
     @WhereString VARCHAR(500) = N'',     --条件语句(不用加where)
     @PageSize    INT,                     --每页多少条记录
     @PageIndex   INT          = 1,     --指定当前为第几页
     @TotalRecord INT OUTPUT            --返回总记录数
    )
    AS
         BEGIN    
        --处理开始点和结束点
             DECLARE @StartRecord INT;
             DECLARE @EndRecord INT;
             DECLARE @TotalCountSql NVARCHAR(500);
             DECLARE @SqlString NVARCHAR(2000);
             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+'  where '+@WhereString;
                     SET @SqlString = @SqlString+'  where '+@WhereString;
             END;
        --返回总记录数
             EXEC sp_executesql
                  @totalCountSql,
                  N'@TotalRecord int out',
                  @TotalRecord OUTPUT;
        ----执行主语句
             SET @SqlString = 'select * from '+@SqlString+') as t where rowId between '+LTRIM(STR(@StartRecord))+' and '+LTRIM(STR(@EndRecord));
             EXEC (@SqlString);
         END;

    方式四:

    /*********************************************************  
    * 作    用:数据分页
    * 作    者:Ozawa
    * 作者博客:https://www.cnblogs.com/xiongze520/
    * 创建日期:2019-05-13
    * 使用说明:
          [USP_GetPageData] 'select * from 表名',1,10
    
    
    *********************************************************/

    CREATE PROCEDURE [dbo].[USP_GetPageData] ( @SQLSTR VARCHAR(8000) -- 查询的SQL语句 , @CURPAGE INT -- 当前页面位置 , @PAGESIZE INT -- 页面显示的数据行数 ) AS BEGIN SET NOCOUNT ON DECLARE @P1 INT --游标 , @ROWCOUNT INT , @COUNTPAGE INT , @CurRow INT EXEC sp_cursoropen @P1 OUTPUT, @SQLSTR, @scrollopt = 1, @ccopt = 1, @ROWCOUNT = @ROWCOUNT OUTPUT IF @ROWCOUNT % @PAGESIZE > 0 SET @COUNTPAGE = @ROWCOUNT / @PAGESIZE + 1 ELSE SET @COUNTPAGE = @ROWCOUNT / @PAGESIZE IF @CURPAGE > @COUNTPAGE SET @CURPAGE = @COUNTPAGE SET @CurRow = (@CURPAGE - 1) * @PAGESIZE + 1 SET NOCOUNT OFF SELECT @CURPAGE CURPAEG, @PAGESIZE PageSize, @COUNTPAGE COUNTPAGE, @ROWCOUNT [ROWCOUNT] EXEC sp_cursorfetch @P1, 16, @CurRow, @PAGESIZE SET NOCOUNT ON EXEC sp_cursorclose @P1 END

    还有更多分页方式,感兴趣的可以去查查资料,上面的分页方式已经足够参考了,

    然后结合自身情况可以写一个量身定做的分页方法,后续直接使用就可以了。

    欢迎关注订阅我的微信公众平台【熊泽有话说】,更多好玩易学知识等你来取
    作者:熊泽-学习中的苦与乐
    公众号:熊泽有话说
    出处:https://www.cnblogs.com/xiongze520/p/10855312.html
    创作不易,任何人或团体、机构全部转载或者部分转载、摘录,请在文章明显位置注明作者和原文链接。  

     

  • 相关阅读:
    739. Daily Temperatures
    556. Next Greater Element III
    1078. Occurrences After Bigram
    1053. Previous Permutation With One Swap
    565. Array Nesting
    1052. Grumpy Bookstore Owner
    1051. Height Checker
    数据库入门及SQL基本语法
    ISCSI的概念
    配置一个IP SAN 存储服务器
  • 原文地址:https://www.cnblogs.com/xiongze520/p/10855312.html
Copyright © 2020-2023  润新知