用存储过程实现分页

最新推荐文章于 2020-11-20 12:30:37 发布

「已注销」

最新推荐文章于 2020-11-20 12:30:37 发布

阅读量597

点赞数

分类专栏：数据库文章标签：存储 sql 优化测试 go c

数据库专栏收录该内容

3 篇文章

订阅专栏

本文介绍两种SQL分页存储过程实现方法：一种未使用ROW_NUMBER()函数，适用于能通过MAX, MIN等方法分页的场景；另一种利用了SQL Server 2005新增的ROW_NUMBER()函数。文中详细解释了如何通过参数自定义分页行为，包括排序方式、返回的字段等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

文件名: GetRecordsPagination.sql
功能: 创建存储过程GetRecordsPagination 注:只能用于能用MAX,MIN等方法的字段进行分页!

1.没有使用ROW_NUMBER()的存储过程

IF EXISTS (
    SELECT *
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE SPECIFIC_NAME = 'GetRecordsPagination')
DROP PROCEDURE GetRecordsPagination

GO

CREATE PROCEDURE GetRecordsPagination
(
    @tableName            varchar(256),       -- 进行分页表名
    @columnName            varchar(256),        -- 用作分页的字段名
    @pageSize            int = 10,           -- 每页记录数 (默认是10)
    @pageIndex            int = 1,            -- 页码 (默认第一页)
    @sortType            bit = 0,            -- 数据查找排序类型,
                                            -- 0 升序,1 降序 (默认 0)
    @visibleColumns        varchar(2000) = '',    -- 要返回的字段,格式"A1,B1,C1" (若为空,则返回全部)
    @sqlCondition        varchar(2000) = '', -- 附加查询条件 (不包含'where'字串)
    @orderByColumn        varchar(256) = '',    -- 记录最后排序OrderBy的字段 (默认等于@columnName)
    @orderBySortType    bit = 0                -- 记录最后排序类型,
                                            -- 0 升序,1 降序 (默认 0)
)
AS

declare @strSQL            varchar(8000)        -- 最终合成的SQL语句
declare @strTemp        varchar(256)        -- 临时字符串
declare @strOrderBy        varchar(1000)       -- 查找排序类型子句

-- 设置数据查找排序类型子句
if @sortType != 0
begin
    set @strTemp = '<(select min'
    set @strOrderBy = ' order by [' + @columnName + '] desc'
end
else
begin
    set @strTemp = '>(select max'
    set @strOrderBy = ' order by [' + @columnName +'] asc'
end

-- 设置要返回字段
if @visibleColumns = ''
    set  @visibleColumns = ' * '

-- 设置查找语句
set @strSQL = 'select top ' + str(@pageSize) + ' ' + @visibleColumns + '  from ['
    + @tableName + '] where [' + @columnName + ']' + @strTemp + '(['
    + @columnName + ']) from (select top ' + str((@pageIndex-1)*@pageSize) + ' ['
    + @columnName + '] from [' + @tableName + ']' + @strOrderBy + ') as TempTable)'
    + @strOrderBy

-- 添加附加查询条件
if @sqlCondition != ''
    set @strSQL = 'select top ' + str(@pageSize) + ' ' + @visibleColumns + '  from ['
        + @tableName + '] where [' + @columnName + ']' + @strTemp + '(['
        + @columnName + ']) from (select top ' + str((@pageIndex-1)*@pageSize) + ' ['
        + @columnName + '] from [' + @tableName + '] where ' + @sqlCondition + ' '
        + @strOrderBy + ') as TempTable) and ' + @sqlCondition + ' ' + @strOrderBy

-- 优化第一页查询
if @pageIndex = 1
begin
    set @strTemp = ''
    if @sqlCondition != ''
        set @strTemp = ' where (' + @sqlCondition + ')'

    set @strSQL = 'select top ' + str(@pageSize) + ' ' + @visibleColumns + '  from ['
        + @tableName + ']' + @strTemp + ' ' + @strOrderBy
end

-- 设置自定义输出OrderBy
if @orderByColumn != ''
begin
    set @strTemp = @strSQL
    if @orderBySortType = 0
        set @strSQL =  ' select * from ( ' + @strTemp + ' ) as TempTable2 order by ['
            + @orderByColumn + '] asc'
    else
        set @strSQL =  ' select * from ( ' + @strTemp + ' ) as TempTable2 order by ['
            + @orderByColumn + '] desc'
end

--测试生成的SQL语句
--PRINT @strSQL

exec (@strSQL)

2.有使用ROW_NUMBER()的存储过程

/***********************************************************************

* 文件名: GetRecordsPaginationWithRowNumber.sql

* 功能: 创建存储过程GetRecordsPaginationWithRowNumber

[注:运用SQL 2005新增函数ROW_NUMBER(),有局限性!]

***********************************************************************/

IF EXISTS (

SELECT *

FROM INFORMATION_SCHEMA.ROUTINES

WHERE SPECIFIC_NAME = 'GetRecordsPaginationWithRowNumber')

DROP PROCEDURE GetRecordsPaginationWithRowNumber

CREATE PROCEDURE GetRecordsPaginationWithRowNumber

(

@tableName varchar(256), -- 进行分页表名

@columnName varchar(256), -- 用作分页的字段名

@pageSize int = 10, -- 每页记录数 (默认是10)

@pageIndex int = 1, -- 页码 (默认第一页)

@sortType bit = 0, -- 数据查找排序类型,

-- 0 升序,1 降序 (默认 0)

@visibleColumns varchar(2000) = '', -- 要返回的字段,格式"A1,B1,C1" (若为空,则返回全部)

@sqlCondition varchar(2000) = '', -- 附加查询条件 (不包含'where'字串)

@orderByColumn varchar(256) = '', -- 记录最后排序OrderBy的字段 (默认等于@columnName)

@orderBySortType bit = 0 -- 记录最后排序类型,

-- 0 升序,1 降序 (默认 0)

)

declare @strSQL varchar(8000) -- 最终合成的SQL语句

declare @strTemp varchar(256) -- 临时字符串

declare @strOrderBy varchar(1000) -- 查找排序类型子句

-- 设置数据查找排序类型子句

if @sortType != 0

begin

set @strOrderBy = ' order by [' + @columnName + '] desc'

end

else

begin

set @strOrderBy = ' order by [' + @columnName +'] asc'

end

-- 设置要返回字段

if @visibleColumns = ''

set @visibleColumns = ' * '

-- 设置查找语句

set @strSQL = 'select top ' + str(@pageSize) + ' ' + @visibleColumns

+ ' from ( select * , ROW_NUMBER() Over ( '

+ @strOrderBy + ' ) as RowNum from [' + @tableName + '] ) as TempTable '

+ ' where RowNum between ' + str( (@pageIndex-1) * @pageSize )

+ ' and ' + str( @pageIndex * @pageSize )

-- 添加附加查询条件

if @sqlCondition != ''

set @strSQL = 'select top ' + str(@pageSize) + ' ' + @visibleColumns

+ ' from ( select * , ROW_NUMBER() Over ( '

+ @strOrderBy + ' ) as RowNum from [' + @tableName + '] where '

+ @sqlCondition + ' ) as TempTable '

+ ' where RowNum between ' + str( (@pageIndex-1) * @pageSize )

+ ' and ' + str( @pageIndex * @pageSize )

-- 优化第一页查询

if @pageIndex = 1

begin

set @strTemp = ''

if @sqlCondition != ''

set @strTemp = ' where (' + @sqlCondition + ')'

set @strSQL = 'select top ' + str(@pageSize) + ' ' + @visibleColumns + ' from ['

+ @tableName + ']' + @strTemp + ' ' + @strOrderBy

end

-- 设置自定义输出OrderBy

if @orderByColumn != ''

begin

set @strTemp = @strSQL

if @orderBySortType = 0

set @strSQL = ' select * from ( ' + @strTemp + ' ) as TempTable2 order by ['

+ @orderByColumn + '] asc'

else

set @strSQL = ' select * from ( ' + @strTemp + ' ) as TempTable2 order by ['

+ @orderByColumn + '] desc'

end

--测试生成的SQL语句

PRINT @strSQL

exec (@strSQL)