简介：面向数据库开发与后端工程师，这份Word文档讲解如何用SQL语言自动生成JSON数据，重点解决SQL Server场景下分页查询结果转换为JSON、供前端程序直接调用或落表存储的问题。资源为单个docx文档，压缩包内共1个文件，整体仅30KB，内容紧凑且便于查阅。文档从JSON基础概念切入，逐步演示动态SQL的构造过程：先定义表名、SQL语句、当前页首行、当前页末行和排序列名等变量，再通过系统视图获取数据表各列名称并拼装字段映射；借助公用表表达式与行号函数对查询结果进行分页排序，使用空值判断避免变量为空，最后执行动态SQL返回包含行号与总记录数的JSON文本。同时，文档给出了将生成的JSON字符串插入数据表的存储写法，以及基于异步请求从接口获取JSON并在前端打印的示例，串联起从数据库查询到前端展示的完整流程。目前已有1143人学习下载，适合具有一定SQL基础、希望提升数据处理与接口交互效率的开发者参考。
1. 把分页查询改成 JSON 输出：一个 SQL Server 老方案的上手拆解
后端接口被前端催着上线，手里只有一张商品表，却要为它单独写一套查询逻辑、再配一个 JSON 序列化工具类——这种场景在内部管理系统里太常见了。用 SQL 自动生成 JSON 数据，直接在数据库层把分页查询结果拼成 JSON 字符串返回，算是不少老开发的首选方案。这份资源的核心思路并不复杂：借助 SYS.SYSCOLUMNS 系统视图读取表结构，把每一列拼成 JSON 键值对，再用 ROW_NUMBER() 做分页编号，最后通过 EXEC 执行动态 SQL，一条语句同时返回当前页数据、行号 rn 和总记录数 total。适合熟悉 T-SQL、希望减少后端序列化代码、或者需要快速交付内部接口的开发者。本文会把这段脚本的每一处拼接逻辑都拆开讲清楚，再给出改造方向和踩坑记录。
2. 方案拆解：为什么在数据库层直接拼接 JSON
2.1 技术选型：系统视图、动态 SQL 与 JSON 字符串
这段脚本能跑起来，依赖 SQL Server 的三个特性：SYS.SYSCOLUMNS 系统视图、ROW_NUMBER() 窗口函数、EXEC 动态语句执行。SYS.SYSCOLUMNS 是 SQL Server 内置的系统视图，里面记录了当前数据库所有表的列信息，WHERE ID = OBJECT_ID(@TableName) 的作用是根据表名拿到表的对象 ID，再过滤出这张表的全部列。相比 INFORMATION_SCHEMA.COLUMNS，它写起来更短，而且列名 name 直接可用，拼接时不需多绕一层。
外层再套 ROW_NUMBER() OVER (ORDER BY ...) 做分页编号，这是 SQL Server 2005 以后就有的窗口函数。配合 WITH paging AS 公共表表达式，把原始表数据先查出来编号，外层再用 BETWEEN 截取某一页的行区间。整个过程不依赖 FOR JSON PATH——SQL Server 2016 之后的原生 JSON 输出语法，所以这套方案在 2008、2012、2014 这些老版本上也能直接跑，这是它最实际的兼容性价值。
真正有技巧的是动态 SQL 的拼接方式。脚本用了一行SELECT @sql = ISNULL(@sql, ...)循环读取所有列名，第一次循环时 @sql 为空，ISNULL 会取第二个参数作为初始前缀，之后每次循环都在末尾追加一列的内容。这种写法比先 SELECT 拼好再 SET 要紧凑，也是老派 T-SQL 开发者常用的手法。
2.2 一次调用的完整链路和变量约定
整套逻辑的运行顺序是这样的：先声明四个核心变量并赋值，然后从 SYS.SYSCOLUMNS 逐行取出表的所有列名，拼出每列的 JSON 键值对片段；接着拼 WITH paging 子查询，加入 ROW_NUMBER() 行号和 COUNT 总数统计；最后再在外层把 rn 和 total 这两个字段补进 JSON 字符串，用 EXEC 执行整段动态 SQL。
四个核心变量的定义和取值方式如下表：
| 变量名 | 类型 | 含义 | 赋值/计算方式 |
|---|---|---|---|
| @TableName | varchar(50) | 要查询的表名 | 直接赋值，如 'Merchandise' |
| @sql | varchar(3000) | 动态拼接的 SQL 字符串 | 不手动赋值，由脚本自动拼接 |
| @CurPageFirstRow | int | 当前页起始行号 | (pageNo-1)*pageSize + 1 |
| @CurPageLastRow | int | 当前页结束行号 | pageNo*pageSize |
| @OrderByColumn | varchar(20) | 分页排序列名 | 直接赋值，如 'MerBarCode' |
这里有一个容易忽略的点：@CurPageFirstRow 和 @CurPageLastRow 的值应该在外部程序里算好再传入，而不是在 SQL 内部做乘法运算。因为这个脚本只管"取第几行到第几行"，不管"第几页第几页"，分页公式放在上层代码里，SQL 的职责更纯粹，也方便直接套用到不同分页组件上。
从职责划分来看，这套方案把三件事压进了数据库：列名到 JSON 键的映射、分页截取、总数统计。后端拿到结果字符串后，只需要做一次 JSON.parse 就能丢给前端，省掉了一个 Service 方法加一个 DTO 类。代价是动态 SQL 的调试难度明显更高，字符串拼接里任何一个引号写错，EXEC 时就直接报语法错误，这也是后面第 5 章要重点排查的部分。
3. 核心模块拆解：一段动态 SQL 如何拼出一个 JSON 数组
3.1 第 1 步：从系统视图读取列名，拼出 JSON 字段片段
整段脚本最核心的部分是从 SYS.SYSCOLUMNS 中循环取列名，逐列拼成 JSON 键值对模板。原始脚本如下，我加了缩进和注释方便阅读：
-- 声明表名和动态 SQL 变量 DECLARE @TableName AS varchar(50) -- 要查询的表 DECLARE @sql AS varchar(3000) -- 拼接字符串，不要给它赋值 DECLARE @CurPageFirstRow int -- 当前页第一行 DECLARE @CurPageLastRow int -- 当前页最后一行 DECLARE @OrderByColumn varchar(20) -- 排序列名 -- 赋值示例 SET @TableName = 'Merchandise' SET @CurPageFirstRow = 1 SET @CurPageLastRow = 20 SET @OrderByColumn = 'MerBarCode' -- 核心：从系统视图逐行取出列名，拼成 JSON 字段片段 SELECT @sql = ISNULL(@sql, 'WITH paging AS ( SELECT REPLACE(''{''+ ') + ''''"''' + name + ''''":'''' + ISNULL(CAST(' + name + ' AS varchar), '''') + ''''",'''' FROM SYS.SYSCOLUMNS WHERE ID = OBJECT_ID(@TableName)这段 SQL 的巧妙之处在SELECT @sql = ISNULL(...)。第一次执行时 @sql 为 NULL，ISNULL 取第二个参数作为初始值，也就是拼出WITH paging AS ( SELECT REPLACE('{'+这个前缀；从第二行开始，@sql 已经有值，ISNULL 直接取 @sql，然后在它末尾继续追加。这样一条 SELECT 语句就完成了循环拼接，不需要写 WHILE 游标。
注意字符串里的单引号嵌套：SQL Server 中字符串内的单引号需要用两个单引号转义，所以''''"'''实际表示的是'"这一段。每取到一个列名 name，就会拼出类似"MerCode":+ CAST(MerCode AS varchar) +",的片段。最外层的 REPLACE 会在后续步骤中把多余的分隔符替换掉，形成正确的 JSON 结构。参数方面，@TableName 可以直接换成任意一张表名，脚本会自动读取该表的全部列，不需要手工维护字段清单。
3.2 第 2 步：拼装分页主查询和总记录数
列名片段拼完后，接下来要把分页主体接上去。这段负责把前面拼好的字段片段包进一个名为 paging 的公共表表达式，同时计算每行数据的行号 rn 和整表总数 total：
-- 在字段片段末尾追加分页主查询 SET @sql = @sql + '' + ','''''''','''' ) + ' AS sourceTable, ROW_NUMBER() OVER (ORDER BY ' + @OrderByColumn + ') AS rn, (SELECT COUNT(' + @OrderByColumn + ') FROM ' + @TableName + ') AS total FROM ' + @TableName + ' ) SELECT * FROM paging WHERE rn BETWEEN ' + CAST(@CurPageFirstRow AS varchar) + ' AND ' + CAST(@CurPageLastRow AS varchar)先说 REPLACE 的作用。前面每列拼接完后，每一行数据的末尾会残留一个多余的逗号和引号，REPLACE 在这里把,+'这个组合替换成空字符串，让每一行数据的 JSON 字段之间用逗号正确分隔。position 上,'''''''','''' )的写法其实是把行数据中最后一个字段末尾的",修正为"，使得单行数据的 JSON 对象闭合。
然后是 ROW_NUMBER() 和 COUNT 的搭配。ROW_NUMBER() OVER (ORDER BY @OrderByColumn) 会按指定列排序后生成递增的行号，外层 WHERE rn BETWEEN 就完成了分页截取。COUNT(@OrderByColumn) 用来统计总记录数，但这里有个隐藏问题：如果 @OrderByColumn 列中存在 NULL 值，COUNT 会跳过这些行，导致 total 偏小。稳妥做法是改成 COUNT(*)，保证总数统计永远基于整表行数。
CAST(@CurPageFirstRow AS varchar)把 int 类型的页码变量转成字符串拼进动态 SQL，这一步很容易被新手漏掉——动态 SQL 里不能直接拼接 int 类型的变量，必须先转 varchar 或 nvarchar，否则 SQL Server 会做隐式转换，轻则报错，重则拼出错误内容。
3.3 第 3 步：外层补全 rn 和 total，EXEC 执行
有了 paging 子查询和分页条件后，还差最后一步：把 rn 行号和 total 总数也拼进每一行 JSON 数据里，使输出结果成为一个完整的 JSON 数组结构：
-- 外层 SELECT 把 rn 和 total 补进 JSON 字符串 SET @sql = @sql + '' + 'SELECT sourceTable + ''"rn":"'' + CAST(rn AS varchar) + ''","total":"'' + CAST(total AS varchar) + ''"},'' FROM paging WHERE rn BETWEEN ' + CAST(@CurPageFirstRow AS varchar) + ' AND ' + CAST(@CurPageLastRow AS varchar) -- 执行动态 SQL EXEC(@sql)这段的逻辑是：paging 里每一行已经存了 sourceTable（即前面拼好的行数据 JSON 片段）、rn（行号）和 total（总数）。外层 SELECT 使用字符串拼接，把"rn":"1","total":"156"}接在 sourceTable 后面，形成一条完整的 JSON 对象记录。每行末尾的逗号让输出结果呈现为 JSON 数组的分隔风格，前端拿到后把最外层去掉或直接按数组解析即可。
执行方式是 EXEC(@sql)，括号写法表示执行动态 SQL。需要注意，这里直接输出结果集，没有做额外的 FORMAT 或格式化处理，所以每次调用返回的是一个字符串列。如果表里有大字段或者列特别多，@sql 声明为 varchar(3000) 很容易不够用，建议从一开始就改成 varchar(max)，避免列数超过 3000 字符时静默截断。然而这种截断不会报错，只会在执行时报"语法错误"或返回残缺数据，排查起来相当费劲。
3.4 输出效果：一条 SQL 返回的 JSON 长什么样
以商品表为例，假设 Merchandise 有 MerCode、MerName 两列，执行以上脚本后返回的结果大致如下：
{"MerCode":"001","MerName":"苹果","rn":"1","total":"156"}, {"MerCode":"002","MerName":"香蕉","rn":"2","total":"156"}, {"MerCode":"003","MerName":"橘子","rn":"3","total":"156"},注意几个特征：所有值都用双引号包裹，包括数字类型的 rn 和 total；每条记录末尾带逗号，整体不是严格的 JSON 数组格式（最外层没有方括号，最后一行有多余逗号）。如果需要严格合法的 JSON 数组，外层还得用字符串函数去掉最后一个逗号并加上方括号。前端在使用时，可以直接在 JS 里对返回文本做处理，或者把末尾逗号去掉后 JSON.parse。
这里还要提醒一句：原脚本中在拼接键名时写的是\"带反斜杠的转义写法，生成的结果里会出现{\"MerCode\":\"001\"这种内容。部分 JSON 解析器对反斜杠容忍度较低，建议在实际改造时把反斜杠去掉，让键名和值都使用纯双引号。
4. 运行验证与接入流程：从 SSMS 到应用层
4.1 在 SSMS 中直接跑通这套脚本
拿到这段脚本后，最快验证方式就是打开 SQL Server Management Studio，连上任意一个测试库，把脚本粘进查询窗口执行。第一步先把 @TableName 改成实际存在的表名，@OrderByColumn 改成表中确实存在的列名，@CurPageFirstRow 和 @CurPageLastRow 按分页公式算好。
完整执行脚本如下：
DECLARE @TableName AS varchar(50) DECLARE @sql AS varchar(max) DECLARE @CurPageFirstRow int DECLARE @CurPageLastRow int DECLARE @OrderByColumn varchar(20) SET @TableName = 'Merchandise' SET @CurPageFirstRow = 1 SET @CurPageLastRow = 20 SET @OrderByColumn = 'MerBarCode' SELECT @sql = ISNULL(@sql, 'WITH paging AS ( SELECT REPLACE(''{''+ ') + ''''"''' + name + ''''":'''' + ISNULL(CAST(' + name + ' AS varchar), '''') + ''''",'''' FROM SYS.SYSCOLUMNS WHERE ID = OBJECT_ID(@TableName) SET @sql = @sql + '' + ','''''''','''') + ' AS sourceTable, ROW_NUMBER() OVER (ORDER BY ' + @OrderByColumn + ') AS rn, (SELECT COUNT(*) FROM ' + @TableName + ') AS total FROM ' + @TableName + ' ) SELECT sourceTable + ''"rn":"'' + CAST(rn AS varchar) + ''","total":"'' + CAST(total AS varchar) + ''"},'' FROM paging WHERE rn BETWEEN ' + CAST(@CurPageFirstRow AS varchar) + ' AND ' + CAST(@CurPageLastRow AS varchar) EXEC(@sql)执行成功后，结果窗口会返回一行或多行 JSON 字符串。我习惯在验证阶段先不直接 EXEC，而是把最后一句改成SELECT @sql，先看拼出来的完整 SQL 长什么样，确认引号、表名、列名都没有问题后再执行。这一步能省下大量排查时间。
4.2 封装成存储过程接入应用程序
直接贴 SQL 到代码里虽然能用，但不推荐。更好的做法是把整段脚本封装成一个存储过程，外部程序只要传入表名、页码、页大小和排序列就能拿到 JSON 结果：
CREATE PROCEDURE usp_GetPageJson @TableName varchar(50), @PageNo int, @PageSize int, @OrderByColumn varchar(20) AS BEGIN DECLARE @sql AS varchar(max) DECLARE @CurPageFirstRow int DECLARE @CurPageLastRow int SET @CurPageFirstRow = (@PageNo - 1) * @PageSize + 1 SET @CurPageLastRow = @PageNo * @PageSize SELECT @sql = ISNULL(@sql, 'WITH paging AS ( SELECT REPLACE(''{''+ ') + ''''"''' + name + ''''":'''' + ISNULL(CAST(' + name + ' AS varchar), '''') + ''''",'''' FROM SYS.SYSCOLUMNS WHERE ID = OBJECT_ID(@TableName) SET @sql = @sql + '' + ','''''''','''') + ' AS sourceTable, ROW_NUMBER() OVER (ORDER BY ' + @OrderByColumn + ') AS rn, (SELECT COUNT(*) FROM ' + @TableName + ') AS total FROM ' + @TableName + ' ) SELECT sourceTable + ''"rn":"'' + CAST(rn AS varchar) + ''","total":"'' + CAST(total AS varchar) + ''"},'' FROM paging WHERE rn BETWEEN ' + CAST(@CurPageFirstRow AS varchar) + ' AND ' + CAST(@CurPageLastRow AS varchar) EXEC(@sql) END调用方式：
EXEC usp_GetPageJson 'Merchandise', 2, 20, 'MerBarCode'存储过程的最大好处在于参数化。表名和列名虽然没有办法完全防注入（动态 SQL 的固有缺陷），但至少应用层不用自己拼 SQL，前后端的分页公式也统一收敛到一处。实际项目中，我一般还会加一个 @WhereSql 参数，允许调用方传入额外的过滤条件，比如WHERE Status = 'Active'，让这套存储过程能覆盖更多查询场景。
4.3 将 JSON 数据写入数据表
除了直接返回给前端，也可以把生成的 JSON 落库，方便后续做数据归档或异步处理。存储到表里时，需要先准备一张接收表，再用 INSERT INTO 插入：
-- 建一张接收 JSON 的表 CREATE TABLE MyTable ( Id int IDENTITY(1,1) PRIMARY KEY, JsonData nvarchar(max), CreateTime datetime DEFAULT GETDATE() ) -- 把动态 SQL 生成的 JSON 字符串插入表中 DECLARE @json nvarchar(max) -- 这里先执行动态 SQL，将结果集赋值给 @json -- 常见做法是把动态 SQL 改成 SELECT @json = 拼接结果，再执行 INSERT INSERT INTO MyTable (JsonData) VALUES (@json)需要注意，原文示例中的INSERT INTO MyTable (JsonData) VALUES (@sql)并不严谨，因为 @sql 是动态 SQL 语句本身，而不是查询结果。正确做法是先通过SELECT @json = ...把拼接结果取出来，或者把动态 SQL 改成SELECT @json = sourceTable + ... FROM paging再执行，随后才能把 @json 插入表中。这个差别很隐蔽，但决定了落库的是 JSON 数据还是拼错的 SQL 文本，写代码时一定要区分清楚。
5. 避坑与排查：动态 SQL 拼接的五个血泪教训
5.1 字符串截断：varchar(3000) 装不下拼接结果
现象：字段比较多的表执行报错，错误信息显示在 SQL 语句末尾附近，有时直接提示 "Unclosed quotation mark" 或 "Incorrect syntax near ..."。
原因：@sql 被声明为 varchar(3000)，当表有几十个列、每列拼出一长段 JSON 片段后，总长度很容易超过 3000 字符。SQL Server 对字符串变量的截断是静默的，不会报"长度不足"，而是把后半段丢掉，导致拼出来的 SQL 语法残缺。
解决：把 @sql 的类型改成 varchar(max) 或 nvarchar(max)。同时建议在调试阶段先执行SELECT @sql查看完整拼接结果，确认无误后再 EXEC。这是最基础的排查手段，却能让动态 SQL 的调试效率翻倍。
5.2 JSON 键名出现反斜杠：解析器不认账
现象：生成的 JSON 字符串形如{\"MerCode\":\"001\"}，部分前端框架或后端 JSON 库直接报解析错误。
原因：原脚本在拼接键名时写成了\"，SQL Server 会把反斜杠原样保留在字符串中。在 JSON 规范里，双引号前不需要也不应该加反斜杠（除非它处于字符串值内部）。
解决：把拼接模板中的\"改为"，即从'''\"'''改为'''"'''。改完后再看输出，键名就是干干净净的"MerCode"而不是\"MerCode\"。
5.3 COUNT 排序列导致总数偏小
现象：total 字段返回的数字比表实际行数小，而且不固定，有时少一行，有时少好几行。
原因：脚本里用的是SELECT COUNT(@OrderByColumn) FROM ...。COUNT 只统计非 NULL 值，如果排序列中有 NULL，这些行会被跳过，总数自然偏小。
解决：把 COUNT 参数改成星号，即COUNT(*)。这样统计的就是整表行数，不受任何列空值影响。顺手还要确认 @OrderByColumn 最好选非空且有唯一约束的列，否则 ORDER BY 结果不稳定，分页可能会出现重复数据或跳行。
5.4 动态 SQL 里的单引号：拼出来的语句被"截断"
现象：表里某条数据的文本字段包含单引号，比如商品名是 "Men's Shirt"。执行时直接报语法错误，错误位置正好在该行数据附近。
原因：SQL Server 字符串中的单引号需要在动态 SQL 里转义为两个单引号，但脚本里的值字段只做了 CAST，没有做替换处理，导致单引号提前终结了字符串字面量，后续 SQL 文本被当作命令解析。
解决：在拼接值部分增加一个 REPLACE，把单引号替换为两个单引号：REPLACE(CAST(列名 AS varchar), '''', '''''')。如果有条件使用 SQL Server 2016 以上的原生 JSON 支持，直接改用 FOR JSON PATH 或 JSON_MODIFY，系统会自动处理转义，不用手工拼字符串。
5.5 换到其他数据库跑不通：语法完全不兼容
现象：把脚本原样丢到 MySQL 或 PostgreSQL 里执行，第一行就报 syntax error，SYS.SYSCOLUMNS 找不到。
原因：这是 SQL Server 专属方案。SYS.SYSCOLUMNS、OBJECT_ID、EXEC 都是 T-SQL 特性，ROW_NUMBER() 虽然其他数据库也有，但整体语法和系统视图无法移植。
解决：如果项目跑在 SQL Server 上，这套方案可以直接用；如果后端是 MySQL，应该改用 CONCAT 和 JSON_OBJECT 拼 JSON，或用 GROUP_CONCAT 实现列名循环。如果项目本身就是多数据库兼容，趁早放弃在数据库层拼 JSON 的念头，回到后端用通用 JSON 库序列化，反而省心。这类兼容性问题一旦上线后才发现，基本等于重写，我经常把它当作选型阶段的第一道过滤条件。
6. 二次开发：加过滤条件、排序方向与格式规范化
这套脚本的改造空间主要集中在三个方向：过滤条件、排序方向、JSON 格式收敛。先说过滤条件，我一般会在存储过程里加一个 @WhereSql 参数，允许调用方传入 WHERE 片段，在拼 WITH paging 主查询时用 ISNULL 拼进去：
-- 拼主查询时增加过滤条件片段 SET @sql = @sql + ' FROM ' + @TableName + CASE WHEN ISNULL(@WhereSql, '') <> '' THEN ' WHERE ' + @WhereSql ELSE '' END + ' ) SELECT sourceTable + ... FROM paging WHERE rn BETWEEN ...'排序方向可以再加一个 @SortDirection 参数，传入 ASC 或 DESC，拼 ORDER BY 时拼接进去。这里要注意 @SortDirection 必须做白名单校验，避免拼接恶意内容。JSON 格式规范化的改动核心是把最外层套上方括号，并去掉最后一条记录末尾的逗号，这一步可以在外层 SELECT 时用 STUFF 或 LEFT + LEN 处理。
改动完记得做两组验证：第一组用 1 到 5 页逐页对比数据，确认分页不重不漏；第二组在表里人为插入 NULL 值和含单引号的文本，确认 JSON 仍然能正确解析。从那以后，我每次写这种动态 SQL，都会强制走一遍"先 SELECT 看拼接结果，再 EXEC 看执行结果"的流程，再也不敢直接拿生成的 SQL 去生产环境试错。这套脚本的价值不在代码本身，而在于它帮我养成了一套调试动态 SQL 的固定习惯，希望帮到你。
本文还有配套的精品资源，点击获取
