SQL通用分页存储过程

喜欢ヅ旅行 2022-04-24 04:38 430阅读 0赞

# [SQL通用分页存储过程][SQL] #

SQL2000

/*********************************************************************************
    *      Function:  SqlPage
    *      Description:
    *             超强通用分页存储过程
    *      Author: sharing
    *      Finish DateTime:2005年9月24日       
    *       Example:
    *              SuperPaging @TableName='表名',@Orderfld='排序列名'           
    *********************************************************************************/
    CREATE PROCEDURE SqlPage
    (
        @TableName        nvarchar(50),            -- 表名
        @ReturnFields    nvarchar(2000) = '*',    -- 需要返回的列 
        @PageSize        int = 10,                -- 每页记录数
        @PageIndex        int = 1,                -- 当前页码
        @Where            nvarchar(2000) = '',        -- 查询条件
        @Orderfld        nvarchar(2000),            -- 排序字段名 最好为唯一主键
        @OrderType        int = 1                    -- 排序类型 1:降序 其它为升序
        
    )
    AS
        DECLARE @TotalRecord int
        DECLARE @TotalPage int
        DECLARE @CurrentPageSize int
        DECLARE @TotalRecordForPageIndex int
        DECLARE @OrderBy nvarchar(255)
        DECLARE @CutOrderBy nvarchar(255)
        
        if @OrderType = 1
            BEGIN
                set @OrderBy = ' Order by ' + REPLACE(@Orderfld,',',' desc,') + ' desc '
                set @CutOrderBy = ' Order by '+ REPLACE(@Orderfld,',',' asc,') + ' asc '
            END
        else
            BEGIN
                set @OrderBy = ' Order by ' +  REPLACE(@Orderfld,',',' asc,') + ' asc '
                set @CutOrderBy = ' Order by '+ REPLACE(@Orderfld,',',' desc,') + ' desc '            
            END
        
        
            -- 记录总数
        declare @countSql nvarchar(4000)  
        set @countSql='SELECT @TotalRecord=Count(*) From '+@TableName+' '+@Where
        execute sp_executesql @countSql,N'@TotalRecord int out',@TotalRecord out
        
        SET @TotalPage=(@TotalRecord-1)/@PageSize+1
        SET @CurrentPageSize=@PageSize
            IF(@TotalPage=@PageIndex)
        BEGIN
            SET @CurrentPageSize=@TotalRecord%@PageSize
            IF(@CurrentPageSize=0)
                SET @CurrentPageSize=@PageSize
        END
        -- 返回记录
        set @TotalRecordForPageIndex=@PageIndex*@PageSize
        exec('SELECT * FROM
            (SELECT TOP '+@CurrentPageSize+' * FROM
                (SELECT TOP '+@TotalRecordForPageIndex+' '+@ReturnFields+'
                FROM '+@TableName+' '+@Where+' '+@OrderBy+') TB2
            '+@CutOrderBy+') TB3
                  '+@OrderBy)
        -- 返回总页数和总记录数
        SELECT @TotalPage as PageCount,@TotalRecord as RecordCount
    GO

SQL2005

/*********************************************************************************
    *      Copyright (C) 2006 All Rights Reserved                         *
    *      Function:  SqlPage                                                     *
    *      Description:                                                              *
    *             Sql2005分页存储过程                                                 *
    *      Author:                                                                   *
    *             sharing                                                         *
    *      Finish DateTime:                                                          *
    *             2006/7/15                                                             *
    *       Example:                                                                     *
    *              SqlPage @Tablename = 'Table1', @Returnfields = '*',           *
    *              @PageSize = 2, @PageIndex = 1, @Where = '', @Orderfld = 'id',      *
    *              @Ordertype = 0                                                     *           
    *********************************************************************************/
    CREATE PROCEDURE dbo.SqlPage
        @TableName        nvarchar(200),            -- 表名
        @ReturnFields    nvarchar(200) = '*',    -- 需要返回的列 
        @PageSize        int = 10,                -- 每页记录数
        @PageIndex        int = 1,                -- 当前页码
        @Where            nvarchar(200) = '',        -- 查询条件
        @Orderfld        nvarchar(200),            -- 排序字段名 最好为唯一主键
        @OrderType        int = 1                    -- 排序类型 1:降序 其它为升序
    AS
        DECLARE @TotalRecord int
        DECLARE @TotalPage int
        DECLARE @CurrentPageSize int
        DECLARE @TotalRecordForPageIndex int
        DECLARE @OrderBy nvarchar(255)
        
        if @OrderType = 1
            BEGIN
                set @OrderBy = ' Order by ' + REPLACE(@Orderfld,',',' desc,') + ' desc '
            END
        else
            BEGIN
                set @OrderBy = ' Order by ' + REPLACE(@Orderfld,',',' asc,') + ' asc '        
            END
        
        -- 记录总数
        declare @countSql nvarchar(4000)  
        
        set @countSql='SELECT @TotalRecord=Count(*) From '+@TableName+' '+@Where
        execute sp_executesql @countSql,N'@TotalRecord int out',@TotalRecord out
        
        
        
        SET @TotalPage=(@TotalRecord-1)/@PageSize+1
        
        SET @CurrentPageSize=(@PageIndex-1)*@PageSize
    
            
        -- 返回记录
        set @TotalRecordForPageIndex=@PageIndex*@PageSize
        
        exec    ('SELECT *
                FROM (SELECT TOP '+@TotalRecordForPageIndex+' '+@ReturnFields+', ROW_NUMBER() OVER ('+@OrderBy+') AS Supesoft_RowNo
                FROM '+@TableName+ ' ' + @Where +' ) AS TempSqlPageTable
                WHERE TempSqlPageTable.Supesoft_RowNo > 
                '+@CurrentPageSize)
    
    
        -- 返回总页数和总记录数
        SELECT @TotalPage as PageCount,@TotalRecord as RecordCount

posted @ 2019-01-25 09:39 [波霸38][38] 阅读( ...) 评论( ...) [编辑][Link 1] 收藏

[SQL]: https://www.cnblogs.com/sharing1986687846/p/10318058.html
[38]: https://www.cnblogs.com/sharing1986687846/
[Link 1]: https://i.cnblogs.com/EditPosts.aspx?postid=10318058