SQL Server 存储过程分页方案

核心原理与适应场景

该存储过程 p_show 的核心逻辑在于根据数据源的底层结构选择最优解，避免全表扫描或低效排序：

1. 第一页特殊处理：若请求的是第一页，直接使用 TOP 关键字返回结果，效率最高。
2. 有唯一主键或标识列：优先使用此类字段进行排除法查询（NOT IN），减少中间结果集的大小。
3. 有复合主键：当主键由多个字段组成时，无法直接用于简单排除，因此采用自连接（Self-Join）的方式，先取出前 N+M 条记录的主键组合，再 LEFT JOIN 排除前 N 条，从而精准定位目标页数据。
4. 无主键/非表对象：对于视图、派生表或无主键的基础表，创建一个带有自增标识列（IDENTITY）的临时表 #tb，通过范围查询（BETWEEN）截取指定页码的数据。

准备工作：存储过程安装

在执行分页前，需先在数据库中创建存储过程。以下代码包含了删除旧版本（若存在）及创建新版本的完整逻辑。请注意，该脚本使用了较早期的系统视图（如 sysobjects, sysindexes），在 SQL Server 2005 之前的版本中兼容性最好；若在高版本使用，建议测试后迁移至基于 DMV 的现代写法以获取更好性能。

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[p_show]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[p_show]
GO

CREATE PROC p_show
    @QueryStr NVARCHAR(4000),       -- 表名、视图名或查询语句
    @PageSize INT = 10,             -- 每页大小
    @PageCurrent INT = 1,           -- 当前页码
    @FdShow NVARCHAR(4000) = '',    -- 需要显示的字段列表
    @FdOrder NVARCHAR(1000) = ''    -- 排序字段列表
AS
BEGIN
    DECLARE 
        @FdName NVARCHAR(250),      -- 主键或标识列名
        @Id1 VARCHAR(20), @Id2 VARCHAR(20), -- 开始和结束的记录号
        @Obj_ID INT,                -- 对象ID
        @strfd NVARCHAR(2000),      -- 复合主键列表
        @strjoin NVARCHAR(4000),    -- 连接字段
        @strwhere NVARCHAR(2000);   -- 查询条件

    SELECT @Obj_ID = OBJECT_ID(@QueryStr),
           @FdShow = CASE ISNULL(@FdShow,'') WHEN '' THEN ' *' ELSE ' '+@FdShow END,
           @FdOrder = CASE ISNULL(@FdOrder,'') WHEN '' THEN '' ELSE ' ORDER BY '+@FdOrder END,
           @QueryStr = CASE WHEN @Obj_ID IS NOT NULL THEN ' '+@QueryStr ELSE ' ('+@QueryStr+') a' END;

    -- 如果是第一页，直接用 TOP 完成
    IF @PageCurrent = 1
    BEGIN
        SELECT @Id1 = CAST(@PageSize AS VARCHAR(20));
        EXEC('SELECT TOP '+@Id1+@FdShow+' FROM '+@QueryStr+@FdOrder);
        RETURN;
    END

    -- 判断是否为表，且是否有标识列
    IF @Obj_ID IS NOT NULL AND OBJECTPROPERTY(@Obj_ID,'IsTable')=1
    BEGIN
        SELECT @Id1 = CAST(@PageSize AS VARCHAR(20)),
               @Id2 = CAST((@PageCurrent-1)*@PageSize AS VARCHAR(20));
        
        SELECT @FdName = name FROM syscolumns WHERE id=@Obj_ID AND status=0x80;

        IF @@ROWCOUNT = 0 -- 无标识列，检查主键
        BEGIN
            IF NOT EXISTS(SELECT 1 FROM sysobjects WHERE parent_obj=@Obj_ID AND xtype='PK')
                GOTO lbusetemp;

            SELECT @FdName = name FROM syscolumns WHERE id=@Obj_ID AND colid IN (
                SELECT colid FROM sysindexkeys WHERE @Obj_ID=id AND indid IN (
                    SELECT indid FROM sysindexes WHERE @Obj_ID=id AND name IN (
                        SELECT name FROM sysobjects WHERE xtype='PK' AND parent_obj=@Obj_ID
                    )
                )
            );

            -- 检查是否为复合主键
            IF @@ROWCOUNT > 1
            BEGIN
                SELECT @strfd='', @strjoin='', @strwhere='';
                SELECT @strfd=@strfd+',['+name+']',
                       @strjoin=@strjoin+' AND a.['+name+']=b.['+name+']',
                       @strwhere=@strwhere+' AND b.['+name+'] IS NULL'
                FROM syscolumns WHERE id=@Obj_ID AND colid IN (
                    SELECT colid FROM sysindexkeys WHERE @Obj_ID=id AND indid IN (
                        SELECT indid FROM sysindexes WHERE @Obj_ID=id AND name IN (
                            SELECT name FROM sysobjects WHERE xtype='PK' AND parent_obj=@Obj_ID
                        )
                    )
                );
                SELECT @strfd=SUBSTRING(@strfd,2,2000),
                       @strjoin=SUBSTRING(@strjoin,5,4000),
                       @strwhere=SUBSTRING(@strwhere,5,4000);
                GOTO lbusepk;
            END
        END;
    END
    ELSE
        GOTO lbusetemp;

    /*--使用标识列或单一主键的处理--*/
    lbuseidentity:
        EXEC('SELECT TOP '+@Id1+@FdShow+' FROM '+@QueryStr+
             ' WHERE '+@FdName+' NOT IN(
                 SELECT TOP '+@Id2+' '+@FdName+' FROM '+@QueryStr+@FdOrder+
             )'+@FdOrder);
        RETURN;

    /*--复合主键处理--*/
    lbusepk:
        EXEC('SELECT '+@FdShow+' FROM (
                  SELECT TOP '+@Id1+' a.* FROM (
                      SELECT TOP 100 PERCENT * FROM '+@QueryStr+@FdOrder+
                  ') a
                  LEFT JOIN (
                      SELECT TOP '+@Id2+' '+@strfd+' FROM '+@QueryStr+@FdOrder+
                  ) b ON '+@strjoin+'
              ) a WHERE '+@strwhere);
        RETURN;

    /*--使用临时表处理--*/
    lbusetemp:
        SELECT @FdName='[ID_'+CAST(NEWID() AS VARCHAR(40))+']',
               @Id1 = CAST(@PageSize*(@PageCurrent-1) AS VARCHAR(20)),
               @Id2 = CAST(@PageSize*@PageCurrent-1 AS VARCHAR(20));
        EXEC('SELECT '+@FdName+'=IDENTITY(INT,0,1), '+@FdShow+
             ' INTO #tb FROM '+@QueryStr+@FdOrder+
             ' SELECT '+@FdShow+' FROM #tb WHERE '+@FdName+
             ' BETWEEN '+@Id1+' AND '+@Id2);
END
GO

第1步：调用基础分页示例

这是最简单的调用方式，仅指定表名，使用默认的每页10条数据和默认排序。

EXEC p_show '地区资料'

第2步：指定页码、字段与排序

在实际业务中，通常需要查看第5页，每页显示3条，并按特定条件排序。以下是详细参数的传入方式：

-- 语法: exec p_show '表名', 每页数量, 页码, '显示字段', '排序字段'
EXEC p_show '地区资料', 5, 3, '地区编号, 地区名称, 助记码', '地区编号'

第3步：对复杂查询或视图进行分页

如果数据来源于多表关联查询或视图，必须在查询语句前加上 TOP 100 PERCENT，以确保排序生效（部分 SQL Server 版本中子查询内的 ORDER BY 会被忽略）。

EXEC p_show 'SELECT TOP 100 PERCENT * FROM 地区资料 ORDER BY 地区名称', 5, 3, '地区编号, 地区名称', '地区名称'

关键参数说明

• @QueryStr：输入的数据源。可以是简单的表名，也可以是完整的 SELECT 语句。若是后者，建议外层包上别名或使用括号处理。
• @PageSize：每页显示行数，默认为 10。
• @PageCurrent：当前要查看的页码，默认为 1。计算起始行时遵循公式：(PageCurrent - 1) * PageSize。
• @FdShow：需要输出的列名。若为空，默认输出所有列（*）。若原始数据包含自增 ID 等不适宜展示的字段，可在此处显式指定所需列。
• @FdOrder：排序依据。分页效果高度依赖稳定的排序规则，若不指定可能导致跨页数据混乱。

注意事项与排错

该脚本依赖于 SQL Server 的系统管理视图（System Views）来探测主键和标识列。如果遇到执行错误：

1. 权限问题：确保当前登录账户有权限访问 sysobjects 等相关系统表，通常在 public 角色下已具备读权限。
2. 复合主键漏检：脚本中判断复合主键的逻辑较为特定，若你的数据库架构有特殊的主键定义导致误判进入临时表模式，通常也不会报错，只是性能略有下降。
3. 大数据量性能：当使用临时表模式（lbusetemp）时，会将整表数据导入内存或磁盘临时表，数据量极大（千万级以上）时可能导致 IO 瓶颈。建议针对极大规模数据迁移至 SQL Server 2012+ 引入的 OFFSET...FETCH 标准语法。

以上就是 SQL Server 存储过程实现分页的详细内容，更多关于 SQL Server 数据库管理与优化的资料请关注本站其它相关文章！