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

SQL通用分页存储过程

相关推荐