分页存储过程 SQL2005-优快云博客

本文介绍了一个SQL存储过程，用于实现数据库中的分页查询功能。该过程接受参数如页尺寸、当前页码、表名等，并根据提供的排序和过滤条件生成相应的SQL查询语句。适用于需要从大型数据集中高效检索特定页面数据的应用场景。

CREATE   PROCEDURE   [dbo].[getPage]
@pageSize int,              --页尺寸
@currentPage int,           --当前页
@tableFields varchar(2000), --返回的字段
@tableName   varchar(200), --表名
@orderString varchar(1000), --排序字符串
@whereString varchar(1000), --条件字符串
@IsReCount   bit = 0        -- 返回记录总数, 非 0 值则返回
AS
BEGIN

if @currentPage < 1 set @currentPage = 1

DECLARE   @strSql   varchar(2000)
DECLARE   @strOrder varchar(2000)
DECLARE   @strWhere varchar(2000)

set @strOrder = REPLACE(RTRIM(LTRIM(@orderString)), 'order By ', ' ')
if @strOrder != '' set @strOrder = ' order By ' + @strOrder

set   @strWhere   =   REPLACE(RTRIM(LTRIM(@whereString)), 'where ', ' ')
if   @strWhere   !=   ''
     set   @strWhere   =   '   where   '   +   @strWhere
else
     set   @strWhere   =   '   where 1=1 '

if   @pageSize   =   0
    set   @strSql   =   'select   '   +   @tableFields   +   '   from   '   +   @tableName   +   @strWhere   +   @strOrder
else
    if   @currentPage   =   1
        set   @strSql   =   'select   top( '   +   Str(@pageSize)   +   ')   '   +   @tableFields   +   '   from   '   +   @tableName   +   @strWhere   +   @strOrder
    else
        begin
        set   @strSql   =   'select   top( '   +   Str(@pageSize)   +   ')   *   from   (select   top( '   +   Str(@pageSize   *   @currentPage)   +   ')   '   +   @tableFields   +   ',   ROW_NUMBER()   OVER   ( '
        set   @strSql   =   @strSql   +   @strOrder
        set   @strSql   =   @strSql   +   ')   As   RowNumber   From   '   +   @tableName
        set   @strSql   =   @strSql   +   @strWhere
        set   @strSql   =   @strSql   +   ')   as   t   where   t.RowNumber   >   '   +   Str(@pageSize   *   (@currentPage   -   1))
        set   @strSql   =   @strSql   +   @strOrder
        end

if @IsReCount != 0
set @strSql = 'select count(*) as Total from [' + @tableName + '] ' + @strWhere

exec(@strSql)
END