简介面向需要将 SQL Server 查询结果自动转换为 JSON 的数据库开发人员这份文档兼具原理讲解与可直接参考的代码片段特别适合在报表接口、前后端数据交换、数据导出等场景中使用。内容先说明 JSON 以键值对表示结构化数据的基本规则并以简单示例帮助理解随后重点展开动态 SQL 的构造流程先定义表名、拼接字符串、当前页首行、当前页末行、排序列等变量再通过系统列视图取得表的字段列表结合分页子查询和行编号函数限定结果范围最后使用动态执行语句返回 JSON 格式文本除生成环节外还覆盖将结果用插入语句保存到数据表以及前端通过异步请求调用接口获取数据的完整链路。读者可从中学到一套通用的 SQL 转 JSON 模板减少手工拼接字符串的重复开发也能更清晰地理解变量赋值、行数范围控制、结果格式转换等关键步骤中的注意事项。资源包为 1 个 docx 文档约 30KB目前已有 1143 人学习下载。1. 一条动态SQL把分页查询结果直接变成JSON很多后端项目做到中期都会碰上一个重复劳动表结构一换分页查询接口就要跟着改返回给前端的JSON结构也要重新拼。这份资源给出了一条“绕开ORM、绕开后端序列化”的路——直接在SQL Server里用一段动态SQL把任意表的查询结果拼成JSON字符串分页行号、总行数、排序字段全部由SQL自行算好。它适合正在维护老项目、需要在数据库层快速输出JSON给前端联调的开发者也适合临时把某张表的数据导成JSON做数据对账。整个实现的核心不是查询而是字符串拼接把这条链路拆开看会发现SQL Server的元数据视图和动态执行配合起来能做很多常规查询做不到的事。2. 核心实现拆解动态拼接SQL的三段式流程2.1 五个变量一个分页JSON查询的全部入参先看脚本开头这段变量声明它是整条动态SQL的“入参区”declare TableName as varchar(50) -- 要查询的表名 declare sql as varchar(3000) -- 动态SQL字符串容器不要赋初值 declare CurPageFirstRow int -- 当前页第一行的行号(pageNo-1)*pageSize1 declare CurPageLastRow int -- 当前页最后一行的行号pageNo*pageSize declare OrderByColumn varchar(20) -- 排序列名这里最需要注意的是sql这个变量。它的作用不是存一条写好的SQL而是作为“字符串拼接的容器”在后面的循环和拼接过程中逐步累积出完整的动态SQL文本。注释里特意强调“不要给它赋值”意思是不要写成declare sql as varchar(3000) 。因为后面用了isnull(sql, ...)来判断是否首次拼接如果一开始就给了空字符串isnull的判断会失效首次拼接的起点就会跑偏。两个分页行号CurPageFirstRow和CurPageLastRow也不是SQL Server自动算的它要求调用方在传入之前就算好。常见做法是外部程序算出(pageNo-1)*pageSize1和pageNo*pageSize再传给这一段脚本。如果你打算把它封装成存储过程这两个值可以在存储过程内部用同样的公式直接算出减少外部传参的负担。OrderByColumn控制的是分页排序字段它同时决定了ROW_NUMBER()的排序依据和总行数的count目标字段。这个字段建议选主键或者有唯一索引的列原因在第四章会专门讲。2.2 取列名SYS.SYSCOLUMNS与OBJECT_ID的配合拿到表名之后脚本通过系统视图把这张表的每一列都“扫”出来逐个拼成JSON键值对的片段select sql isnull(sql, with paging as ( select replace({ ) name:isnull(cast(name as varchar),), from SYS.SYSCOLUMNS WHERE ID OBJECT_ID(TableName)这段代码的核心是SYS.SYSCOLUMNS系统视图。它存储了当前数据库所有表的列信息通过OBJECT_ID(TableName)拿到目标表的对象ID再用WHERE ID ...筛选出这张表的所有列。每取到一列就把列名name拼进SQL文本里形成一个类似MerName:这里放列值,的片段。注意isnull(cast(列名 as varchar),)这个细节它做了两件事第一用cast把int、decimal、datetime这些非字符串类型统一转成varchar这样拼出来的JSON值才不会因为类型不匹配而报错第二如果某个字段值是NULL结果会输出空字符串而不是“NULL”四个字母避免前端拿到脏数据。这里有一个容易懵的点select sql sql 片段这种写法在SQL Server里叫“累积赋值”它会遍历SYS.SYSCOLUMNS返回的每一行把每个列对应的JSON片段依次追加到sql末尾。假设Merchandise表有三列MerID、MerName、MerPrice拼完这一步sql大概会变成with paging as ( select replace({ MerID:isnull(cast(MerID as varchar),),MerName:isnull(cast(MerName as varchar),),MerPrice:isnull(cast(MerPrice as varchar),),也就是说动态SQL的“骨架”已经出来了后面只要把分页、排序、总行数补上去整条语句就完整了。2.3 分页与总行数ROW_NUMBER和count如何补全查询列名片段拼完之后脚本用第二个set sql把分页逻辑接上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)这一段的重点有三个。第一个是ROW_NUMBER() OVER (ORDER BY OrderByColumn)它为查询结果按照指定字段排好序后从1开始生成行号这个行号就是后面分页的过滤条件。第二个是(select count(排序列) from 表名)子查询算出这张表的总行数放进JSON里作为total字段前端分页组件需要它来渲染总页数。第三个是where rn between 起始行 and 结束行用行号范围过滤出当前页的数据完成物理分页。原脚本里REPLACE函数和引号嵌套比较绕它的真实意图是处理列名片段拼接后留下的“尾逗号”——每列后面都会跟一个逗号到最后一列时这个逗号就多余了。如果直接把这串文本塞给前端JSON.parse会直接报错。所以要靠REPLACE把最后这个逗号替换成JSON对象结束符}再补上rn和total这两个分页字段整条记录才闭合。这里我一般会建议在调试阶段把EXEC(sql)临时改成PRINT sql把拼出来的SQL文本先打印一遍看看列片段、逗号、括号是否对齐。等到确认拼接无误再改回EXEC执行。这个习惯能省下大量排查引号嵌套的时间。2.4 动态执行EXEC如何输出JSON结果集最后一段是收尾EXEC(sql)动态SQL拼好之后不能直接SELECT sql——那只能输出一段文本不会返回查询结果。必须用EXEC执行SQL Server才会真正运行这段拼接出来的语句并把分页查询结果作为结果集返回。在前端程序或数据库客户端里你能直接看到一列名为sourceTable的JSON字符串每条记录形如{MerID:1,MerName:测试商品A,MerPrice:19.90,rn:1,total:120},注意每条记录尾部自带一个逗号这是为多条记录连续拼接预留的。单条数据单独解析时需要把外层套上[和]或者交给存储过程做进一步结构处理。这一步在第五章做成通用接口时会详细展开。3. 跑通最小示例从建表到看到JSON输出3.1 造一张测试表并写入数据先把整条链路跑通。新建一张简单的商品表插几条数据CREATE TABLE Merchandise ( MerID int primary key, MerName varchar(50), MerPrice decimal(10,2), MerBarCode varchar(20) ); INSERT INTO Merchandise VALUES (1, 测试商品A, 19.90, 6901234567890), (2, 测试商品B, 29.90, 6901234567891), (3, 测试商品C, 39.90, 6901234567892);这里主键MerID是用来保证分页排序稳定性的。如果表里没有主键下面的排序字段就要选一个不重复的列否则翻页时容易出现重复或漏数据的问题。MerBarCode是条码字段后面的分页示例会用它来排序。3.2 按实际表结构调整并执行脚本拿到资源里的原脚本后先不要直接跑按自己的表结构改三处declare TableName as varchar(50) set TableName Merchandise -- 改成你的表名 declare CurPageFirstRow int declare CurPageLastRow int set CurPageFirstRow 1 -- 第1页1到2 set CurPageLastRow 2 declare OrderByColumn varchar(20) set OrderByColumn MerBarCode -- 改成你表里的真实列名如果你是第一次跑这种动态SQL建议先把最后一行的EXEC(sql)注释掉改成PRINT sql先看拼接出来的SQL文本长什么样。这个步骤能确认列名、括号、引号是否正常避免报错时一头雾水。3.3 执行结果与字段说明跑通之后查询结果里会有一列拼接好的JSON字符串内容大致是{MerID:1,MerName:测试商品A,MerPrice:19.90,MerBarCode:6901234567890,rn:1,total:3}, {MerID:2,MerName:测试商品B,MerPrice:29.90,MerBarCode:6901234567891,rn:2,total:3}rn字段代表这条记录在当前排序下的行号可以用来做前端表格的序号列。total字段是这张表的总行数用于计算总页数。这里有一个容易误会的点由于脚本外层只包了单条记录对象没有外层数组括号所以拿到的结果是“多段JSON对象字符串用逗号连接”。前端使用的时候要么在SQL外层拼上[和]要么在后端接收后先拼成合法JSON数组再交给前端。这个问题在第四章也有对应解法。如果执行时报错“无效的列名”多半是OrderByColumn或表名写错了。检查方式仍然是PRINT sql看拼出来的文本里列名前后是否有多余引号或者列名是否真的存在于当前表中。4. 避坑指南动态SQL拼JSON的五个经典问题4.1 单引号转义没处理干净导致整条SQL语法错误现象执行EXEC(sql)时SQL Server报语法错误指向的位置是某段字符串中间。原因在SQL字符串里表示一个单引号要写两个单引号。动态SQL在拼接过程中会产生大量引号嵌套尤其是JSON字符串本身带双引号稍有不慎就多写或少写一个单引号。原脚本中isnull(cast(name as varchar),)这部分实际代表的是一个空字符串的单引号包裹肉眼审阅时很难一眼看清。解决调试时先把EXEC(sql)换成PRINT sql把最终拼出来的SQL文本原样打印到“消息”窗口再复制到新查询窗口检查。每次改动拼接逻辑后都先打印再执行能省掉一半的排查时间。另一种常用做法是不自己拼引号改用QUOTENAME(列名)来包裹对象名引号转义由系统处理。4.2 JSON字符串末尾多出一个逗号前端解析失败现象SQL执行成功但前端拿到数据后调用JSON.parse直接抛异常报错信息指向字符串末尾。原因列名片段循环拼接时每一列结尾都会附带一个逗号最后一列也不列外。原脚本用REPLACE处理了这个问题但如果你的表结构调整了、列数变了替换逻辑可能没覆盖到新的尾部字符尤其是当最后一列后面还有rn和total字段时逗号位置更加微妙。解决不依赖复杂的REPLACE链改成在循环结束后单独处理。常见做法是把所有列片段拼进一个临时字符串最后用STUFF函数删除最后一个逗号或者干脆在最后的SELECT层用FOR JSON PATH来生成合规JSON第六章会讲。处理完以后用JSON.parse验证一遍字符串是否合法。4.3 非字符串列转换后格式不对前端拿到奇怪的值现象datetime类型的列拼进JSON后变成2024 01 01 12:00AM这种格式decimal字段变成19.9000前端展示时需要额外处理。原因脚本里的cast(列 as varchar)对不同类型执行的是SQL Server的默认转换规则datetime按语言环境转成带时间的短格式decimal按精度输出尾部0。解决对特殊类型的列单独处理。比如日期字段先CONVERT(varchar(19), 列, 120)转成2024-01-01 12:00:00金额字段先CONVERT(decimal(10,2), 列)再转varchar。如果你的表里有uniqueidentifier或xml类型直接cast可能报错要改用CONVERT(nvarchar(max), 列)。这部分要根据业务场景写一个列类型白名单遇到过什么类型就补什么分支。4.4 动态SQL超过varchar(3000)被截断输出不完整现象表列很多、表名较长或查询条件复杂时拼接出来的SQL文本长度超过3000执行后返回的JSON字符串后半段内容丢失甚至直接报错。原因原脚本里sql声明为varchar(3000)SQL Server对字符串变量的长度是有限制的超出部分会被悄悄截断不会报“长度不足”的错所以问题很隐蔽。解决把sql改成nvarchar(max)同时注意拼接过程中不要用SET sql sql ...给中间结果做二次赋值避免隐式类型转换再次缩窄长度。如果你还要在此基础上拼更多查询条件建议全程用CONCAT函数拼接并定期PRINT sql查看当前长度。4.5 分页排序字段不唯一翻页出现重复或漏数据现象分页接口第1页和第2页返回的数据有重复或者某些数据在两页中都没出现。原因ROW_NUMBER() OVER (ORDER BY 排序列)要求排序结果严格有序。如果排序列有重复值SQL Server不保证相同值之间的行号顺序稳定翻页时同一行可能被分到不同页。解决排序字段选择主键或唯一索引列。如果业务必须按某个非唯一字段排序就改成复合排序例如ORDER BY 发布时间, 主键ID保证每个行号的唯一性。对OrderByColumn传入外部参数时还要注意防止SQL注入——在存储过程入口校验这个字段是否在允许的列名白名单里。5. 二次开发把脚本改造成通用分页存储过程和API5.1 参数化存储过程去掉外部计算行号原脚本的分页行号需要外部算好传入这对接口层来说不太友好。改造的第一步是把它封装成一个存储过程由存储过程内部通过PageNo和PageSize算出首尾行号CREATE PROCEDURE usp_GetTableJson TableName nvarchar(128), PageNo int 1, PageSize int 20, OrderByColumn nvarchar(128) 1 AS BEGIN SET NOCOUNT ON; DECLARE sql nvarchar(max); DECLARE FirstRow int (PageNo - 1) * PageSize 1; DECLARE LastRow int PageNo * PageSize; -- 校验表存在性避免动态SQL执行时报错 IF OBJECT_ID(TableName) IS NULL BEGIN RAISERROR(表不存在, 16, 1); RETURN; END; -- 拼接JSON列片段逻辑与原脚本一致 SELECT sql ISNULL(sql, ) name : isnull(cast( name as varchar(max)),) , FROM SYS.SYSCOLUMNS WHERE ID OBJECT_ID(TableName); -- 组装分页与总行数 SET sql select replace({ sql },, ) as sourceTable, row_number() over (order by OrderByColumn ) as rn, (select count(*) from TableName ) as total from TableName where row_number()... ; -- 完整拼接略参考原脚本结构 EXEC sp_executesql sql; END封装成存储过程之后外部调用只需要传表名、页码、页大小和排序字段不需要再去计算首尾行号。存储过程内部统一算好接口层少掉一堆拼参逻辑也降低了前端传错行号导致分页异常的概率。5.2 补全JSON数组结构让前端能直接JSON.parse原脚本输出的JSON字符串不是一个合法JSON数组前端拿到后还需要自己包裹。这里建议在存储过程外层做一次补全把多个记录对象包进数组-- 在存储过程内部,把最终结果包成合法JSON数组 SET sql Nselect [ replace( (select replace(来源列, },, },) from 分页结果 for xml path()), ,}, },]) ];如果懒得处理字符串包裹也可以把查询结果先用FOR XML PATH聚合成单行文本再手动加上首尾方括号。这一步做完了前端拿到的就是一段可直接JSON.parse的标准JSON数组每个元素包含业务字段、rn行号和total总行数。total放在每条记录里确实冗余但胜在实现简单前端取第一条的total就行。5.3 前端AJAX联调JSON接口存储过程封装好之后后端只需要暴露一个通用接口把表名、页码、页大小、排序字段透传进来$.ajax({ type: GET, url: /api/getJsonData?tableMerchandisepageNo1pageSize20orderByMerBarCode, dataType: json, success: function(data) { console.log(data); // 渲染表格时,取 data.total 计算总页数 } });这里的table参数在接口层要加白名单校验不能让外部随意传任意表名否则等于把整个库的表数据都暴露了出去。常见做法是在后端维护一张“允许通过接口查询的表清单”接口收到表名后先查清单不在清单里的直接拒绝。5.4 性能说明count子查询与索引的选择动态SQL里的(select count(排序列) from 表名)每次执行都会全表扫描一次表数据量大时性能会明显下降。常见做法是把这个count改成单独一次轻量查询或者在表上对排序列建索引。如果分页接口被频繁调用也可以考虑把总数缓存起来在表数据变更时才重新计算。原脚本的设计思路以“快速拿到JSON”为主生产环境需要根据实际数据量权衡这个优化点。6. 改造落地用FOR JSON PATH替代手写字符串拼接如果你的SQL Server版本支持2016及以上手写REPLACE拼接JSON这套逻辑其实有更稳的替代方案——官方提供的FOR JSON PATH。同样的分页查询可以写成SELECT MerID, MerName, MerPrice, MerBarCode FROM Merchandise ORDER BY MerBarCode OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY FOR JSON PATH;输出直接就是标准JSON数组字段名就是列名NULL值自动处理成null尾部没有多余逗号。配合WITH (ORDER BY)和OFFSET FETCH做分页代码量比手写拼接少一大半也彻底规避了单引号嵌套、尾逗号、cast格式不统一这些老问题。唯一要确认的是生产环境的数据库版本兼容性——SQL Server 2016以下版本不支持FOR JSON老项目只能保留原方案。如果版本够新我的建议是把原脚本作为“理解动态SQL拼接”的学习材料生产逻辑逐步迁移到FOR JSON PATH。但也有一个例外当列名需要动态变化、或者表的列结构经常调整时手写拼接反而更灵活因为FOR JSON PATH必须显式写出列名列表而原脚本可以自动扫描SYS.SYSCOLUMNS适配新列。两种方案各有适用场景按项目实际情况取舍。我之前接手的一个老项目跑在旧版数据库上手写拼接脚本用好几年都没出过问题后来另一个新项目上了新版本环境我图省事直接把老脚本粘过去结果前端JSON.parse不停报错。排查半天发现是版本升级后默认的日期格式变了拼接出来的字符串格式和前端预期不一致。从那以后我每次迁移动态SQL代码都强制先跑一遍PRINT sql、拿真实数据检查一遍输出样例再决定是保留老逻辑还是换新函数绝不因为“以前没出过事”就跳过验证。这个习惯帮我躲过不少类似翻车希望也能帮到你。本文还有配套的精品资源点击获取