SQL Server万能分页存储过程实现与调用

核心问题与解决方案

传统的分页存储过程往往针对特定表编写，例如仅处理 UserName 表。在实际业务中，需要一种能适配不同表结构、支持动态 WHERE 条件和 ORDER BY 的子程序。通过构造动态 SQL 字符串，可以利用 TOP 关键字和子查询逻辑实现“万能”分页效果。

准备条件

• 数据库环境：SQL Server (支持 T-SQL 语法)。
• 前提知识：理解基本的 SQL SELECT 语句、WHERE 子句及变量声明。
• 安全注意：动态 SQL 存在注入风险，生产环境中建议对输入参数进行严格校验或采用参数化查询（本例为展示逻辑提供基础模板）。

第1步：创建万能分页存储过程

目的：创建一个名为 UP_GetRecordByPage 的存储过程，接收表名、主键、分页参数及过滤条件作为输入。

代码逻辑说明： 1. 定义输入参数：@tblName 为表名，@fldName 为主键字段，@PageSize 为每页条数，@PageIndex 为当前页码，@OrderType 决定升降序，@strWhere 为额外的筛选条件（无需包含 where 关键字）。 2. 判断排序类型：根据 @OrderType 决定使用 TOP ... DESC 还是 ASC。 3. 构建动态 SQL：利用嵌套子查询和 TOP 运算法截断前几页数据，从而获取当前页数据。 4. 处理第一页特殊情况：当 @PageIndex = 0 时，直接返回前 N 条记录，简化查询逻辑。

CREATE PROCEDURE [dbo].[UP_GetRecordByPage]
@tblName varchar(255),      -- 表名
@fldName varchar(255),      -- 主键字段名
@PageSize int ,             -- 页尺寸
@PageIndex int ,            -- 页码
@IsReCount bit,             -- 是否返回记录总数, 非 0 值则返回
@OrderType bit,             -- 设置排序类型, 非 0 值则降序, 0 为升序
@strWhere varchar(1000) = ''-- 查询条件 (注意: 不要加 where)
AS
BEGIN
    declare @strSQL varchar(6000) -- 主语句
    declare @strTmp varchar(2000) -- 临时变量(查询条件过长时可能会出错，可修改100为1000)
    declare @strOrder varchar(400) -- 排序类型

    if @OrderType != 0
    begin
        set @strTmp = '<(select min'
        set @strOrder = ' order by [' + @fldName +'] desc'
    end
    else
    begin
        set @strTmp = '>(select max'
        set @strOrder = ' order by [' + @fldName +'] asc'
    end

    set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
        + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
        + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
        + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
        + @strOrder

    if @strWhere != ''
    begin
        set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
            + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
            + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
            + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
            + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
    end

    if @PageIndex <> 0
    begin
        set @strTmp =''
        if @strWhere != ''
            set @strTmp = ' where ' + @strWhere
        set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
            + @tblName + ']' + @strTmp + ' ' + @strOrder
        exec (@strSQL)
    end

    if @IsReCount != 0
    begin
        set @strSQL = 'select count(*) as Total from [' + @tblName + ']'+' where ' + @strWhere
        exec (@strSQL)
    end
END
GO

第2步：调用存储过程并验证结果

目的：通过执行上述存储过程，从指定表中提取特定条件下的分页数据。

操作入口：SQL Server Management Studio (SSMS) 查询窗口或应用程序后端代码。

关键参数解释： - @tblName: 目标表名，如 'UserName'。 - @fldName: 用于排序和定位的主键，如 'ID'。 - @PageSize: 每页显示行数，设为 2。 - @PageIndex: 当前页码，设为 1（第1页）。 - @IsReCount: 设为 0 表示不需要返回总记录数；若需统计总数，设为 1。 - @OrderType: 1 表示降序，0 表示升序。 - @strWhere: 过滤条件，如 'Age=13'，不包含 WHERE 关键字。

EXEC dbo.UP_GetRecordByPage 
    @tblName = 'UserName',   -- varchar(255)
    @fldName = 'ID',         -- varchar(255)
    @PageSize = 2,           -- int
    @PageIndex = 1,          -- int
    @IsReCount = 0,          -- bit (不返回总数)
    @OrderType = 1,          -- bit (降序)
    @strWhere = 'Age=13'     -- varchar(1000)

完成判断与注意事项

执行成功后，查询结果集将返回符合 Age=13 条件的前2条记录（按 ID 降序）。若需调整分页范围，只需更改 @PageIndex 和 @PageSize 即可。 风险提示：由于该存储过程依赖动态 SQL 拼接，若 @strWhere 中包含用户可控的不安全输入，可能导致 SQL 注入。建议在业务层过滤特殊字符，或在高级应用中使用参数化查询重构此逻辑。

简要总结

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