简介面向数据库开发与后端工程师这份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. 方案拆解为什么在数据库层直接拼接 JSON2.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。四个核心变量的定义和取值方式如下表变量名类型含义赋值/计算方式TableNamevarchar(50)要查询的表名直接赋值如 Merchandisesqlvarchar(3000)动态拼接的 SQL 字符串不手动赋值由脚本自动拼接CurPageFirstRowint当前页起始行号(pageNo-1)*pageSize 1CurPageLastRowint当前页结束行号pageNo*pageSizeOrderByColumnvarchar(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 为 NULLISNULL 取第二个参数作为初始值也就是拼出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 和 totalEXEC 执行有了 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 里的单引号拼出来的语句被截断现象表里某条数据的文本字段包含单引号比如商品名是 Mens 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 errorSYS.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 的固定习惯希望帮到你。本文还有配套的精品资源点击获取
