简介：本资源是一份面向SQL Server数据库开发人员与后端工程师的实用技术文档，聚焦于利用T-SQL动态生成标准JSON数据的核心方法，解决传统关系型数据向Web前端或API接口高效输出结构化JSON的常见痛点。文档详细拆解了基于SYS.SYSCOLUMNS元数据自动拼接JSON字符串、结合WITH分页子句与ROW_NUMBER()实现带总条数的分页JSON构造、以及将结果存入表字段或供AJAX调用的完整链路，并附有可直接调试的完整SQL脚本（如Merchandise表分页JSON生成示例）。资源为单个30KB的Word文档（.docx），内容精炼、步骤清晰、代码即贴即用，含变量声明、动态SQL构建逻辑及执行要点说明。目前已有1146人学习下载，适合具备基础T-SQL能力、正需快速落地JSON导出功能的中初级数据库开发者参考实践。
1. SQL 自动生成 JSON：不是“拼字符串就完事”，而是把关系模型到文档模型的映射逻辑硬编码进了 T-SQL 执行计划里
你有没有试过——在 SQL Server 里写一段动态 SQL，把SELECT * FROM Merchandise的结果变成{"MerbarCode":"M001","Name":"iPhone","Price":5999}这样的 JSON 字符串？很多人第一反应是：这不就是+拼接、REPLACE去逗号、CAST转类型、最后套个{}吗？跑起来确实能出结果。但问题来了：当表字段从 5 个涨到 32 个，其中含datetime2、decimal(18,2)、nvarchar(max)和NULL值时，生成的 JSON 突然缺字段、多逗号、引号错位、数字被转成科学计数法，甚至整个字符串因长度超varchar(3000)截断而变成非法 JSON——而你根本没法用PRINT @sql完整看到它到底拼成了啥。这不是玄学，是 T-SQL 动态拼接 JSON 的底层反模式：它把本该由类型系统和序列化引擎承担的职责，强行压给字符串操作；把本该在执行期由查询计划保障的结构一致性，提前挪到编译期靠人肉推演。这份.docx文档里藏着的两段脚本，表面是“SQL 自动生成 JSON”，实质是一套未经抽象、未做逃逸、未隔离上下文的关系行→JSON对象的手动投影协议。搞懂它，不是为了复刻这段代码，而是看清：为什么现代 SQL Server 2016+ 直接加了FOR JSON，为什么 ORM 层宁可多一次网络往返也不愿在数据库里拼 JSON，以及——当你非得在 SQL 层做这件事时，哪些边界条件会直接让EXEC(@sql)变成黑匣子翻车现场。
2. 投影逻辑：SYS.SYSCOLUMNS 如何把表结构翻译成 JSON 键值对模板
这段脚本的核心动作，不是执行查询，而是在运行前构造一个能输出 JSON 字符串的 SELECT 表达式。它没调用任何 JSON 函数，全靠+、ISNULL、CAST和REPLACE在字符串层面模拟序列化行为。要理解它怎么 work，得先拆开它的“元编程”层：它如何从一张表的物理定义，生成对应字段的 JSON key-value 模板。
2.1 SYS.SYSCOLUMNS：SQL Server 2000 风格的元数据视图及其隐含假设
脚本中这句：
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（注意：不是sys.columns），按ID关联目标表。这里埋着第一个关键假设：所有字段都必须能无损 cast 成varchar。SYS.SYSCOLUMNS是兼容层视图，其type列返回的是老式系统类型 ID（如 56=INT, 106=DECIMAL），但它不暴露精度、小数位、是否允许 NULL 等细节。脚本直接cast('+name+' as varchar)，意味着：
datetime2(7)→CAST(... AS varchar)默认截断为yyyy-mm-dd hh:mi:ss，毫秒丢失；decimal(18,4)→ 若值为123456789012345.1234，varchar默认长度可能不足，触发隐式截断；xml或varbinary类型字段会直接报错，因为CAST(xml_col AS varchar)不被允许。
提示：
SYS.SYSCOLUMNS已在 SQL Server 2005+ 中标记为“向后兼容”，官方推荐用sys.columns+sys.types组合。但此脚本选择它，恰恰因为它返回的name是纯标识符，无需额外处理 schema 前缀，降低了动态 SQL 构造复杂度——这是为“能跑”做的妥协，不是为“健壮”做的设计。
2.2 字符串模板的生成逻辑：replace('{'+ + ',"' 实际在做什么？
看这句核心拼接：
replace('{'+ ',"'+name+'\":\"''+isnull(cast('+name+' as varchar),'''')+''\",'它不是在生成 JSON，而是在生成一个会被最终SELECT执行的字符串表达式。我们把它展开一层：
- 假设字段名是
MerbarCode，类型是varchar(20)，值为'M001'； - 拼出的片段是：
'"MerbarCode":"'+isnull(cast(MerbarCode as varchar), '')+'"'; - 当
SELECT执行时，这一列实际计算的是：'"MerbarCode":"' + 'M001' + '"'→'"MerbarCode":"M001"'
注意：replace('{'+ ...中的{和开头的replace并非用于 JSON 格式化，而是为后续REPLACE(sourceTable, ',,', ',')做准备——它先把所有字段用,"key":"val",拼起来，开头塞{，结尾留,，最后用REPLACE(..., ',,', ',')去掉末尾多余逗号。这是一种典型的“先污染后治理”字符串工程。
但问题在于：REPLACE是在sourceTable字符串上操作，而sourceTable是整行拼接结果。如果某字段值本身含,"（比如产品描述里写了"尺寸：12\" x 8\""），REPLACE会误删合法内容。这不是 bug，是设计盲区：它把业务数据内容和 JSON 结构符号混在同一字符串层级处理，彻底放弃了语义分隔。
2.3 分页与元信息注入：ROW_NUMBER() 和 total 计数如何破坏 JSON 合法性
脚本第二段在sourceTable后追加了rn和total：
sourceTable+''\"rn\":\"''+ cast(rn as varchar)+''\",\"total\":\"''+cast( total as varchar)+''\"},''这导致最终 JSON 变成：
{"MerbarCode":"M001","Name":"iPhone","rn":"1","total":"123"},注意结尾的,和没有闭合的{。它依赖外部再包一层SELECT '{' + ... + '}'，但脚本里没写——这意味着：这段 SQL 生成的不是单个 JSON 对象，而是一堆以逗号分隔的 JSON 片段。真正的完整 JSON 必须由调用方（比如 C# 代码）再做一次string.Join(",", results)+"[...]"封装。否则，直接EXEC返回的是非法 JSON 流。这也是为什么文档里说“用于前端程序调用”，却没给出完整 HTTP 响应示例——它默认使用者会补全这个封装层。
3. 执行机制：EXEC(@sql) 如何绕过查询优化器，把类型风险甩给运行时
动态 SQL 的EXEC(@sql)看似只是“执行字符串”，但在 SQL Server 内部，它触发了一套完全独立于普通批处理的执行路径。理解这点，才能明白为什么这段脚本在开发环境能过，在生产环境必崩。
3.1 编译期 vs 运行期：@sql 变量的生命周期决定了错误捕获时机
普通静态 SQL 在CREATE PROCEDURE时即完成语法检查、对象绑定、执行计划缓存。而EXEC(@sql)的内容直到运行时才被解析。这意味着：
- 表名
@TableName在OBJECT_ID(@TableName)调用时才校验是否存在； - 字段名
@OrderByColumn在ORDER BY '+@OrderByColumn拼入后，才在EXEC时验证是否属于该表； cast('+name+' as varchar)中的name是字符串，CAST错误（如对xml字段强转）只在EXEC执行 SELECT 时抛出。
换句话说：所有类型安全、对象存在性、权限检查，都被推迟到最晚时刻。你无法在写存储过程时SET NOEXEC ON预检，也无法用 SSMS 的“分析执行计划”看它会怎么跑。它是一个真正的黑匣子——直到EXEC那一刻，SQL Server 才开始编译、优化、执行这个动态生成的查询。
3.2 执行计划缓存失效：为什么每次分页参数变，都要重编译
SQL Server 对EXEC(@sql)的执行计划缓存策略是：以 @sql 字符串内容为 key。也就是说：
- 第一次
@CurPageFirstRow=1, @CurPageLastRow=20→ 生成... where rn between 1 and 20→ 编译缓存 Plan A； - 第二次
@CurPageFirstRow=21, @CurPageLastRow=40→ 生成... where rn between 21 and 40→ 视为全新字符串 → 编译 Plan B；
即使逻辑完全相同（都是BETWEEN ? AND ?），只要字符串不同，就绝不复用计划。这对高并发分页场景是灾难：CPU 花在反复编译上，而不是执行。而原生OFFSET-FETCH或FOR JSON的执行计划是参数化的，@offset和@fetch值变，计划复用率接近 100%。
3.3 字符串长度陷阱：varchar(3000) 不是上限，而是悬崖
脚本声明declare @sql as varchar(3000)，但实际拼接内容远超此限：
- 每个字段贡献约
len('"key":"")+len(value)+len('",')； Merchandise表若 20 字段，平均值长 50 字符 → 仅字段部分就需20 * (6+50+3) = 1180；- 加上
WITH paging AS (...) SELECT ... FROM paging WHERE rn BETWEEN ...模板 → 轻松突破 2500； - 一旦
@sql超过 3000，T-SQL 自动截断，且不报错、不警告，你拿到的是一段语法错误的 SQL，EXEC直接失败。
更隐蔽的是：varchar(3000)在 SQL Server 中默认是varchar(3000)，但若数据库兼容级别 < 130，varchar参数默认最大 8000；若 > 130，varchar无显式长度时默认为varchar(1)。脚本没写varchar(max)，是典型的历史债务写法。
4. 避坑：5 条血泪经验总结——那些让EXEC(@sql)在凌晨三点把你叫醒的坑
这段脚本在小表、固定字段、无特殊类型时能跑通，但一旦进入真实业务场景，就会触发一系列“看似合理、实则致命”的连锁故障。以下是某开发者在某跨平台系统上线前压测时踩出的 5 个典型坑，每条都附带复现方式、根因和修复逻辑：
4.1 现象：JSON 中出现"Price":"1.23456789012345e+006"而非"Price":"1234567.89"
- 原因：
cast(Price as varchar)对decimal(18,2)类型未指定长度，SQL Server 使用默认varchar长度（通常 30），当数值过大时触发科学计数法转换； - 解决：显式指定长度，如
cast(Price as varchar(20))，并确保长度 ≥ 整数位+小数位+1（负号）；更优解：用FORMAT(Price, '0.##')（SQL Server 2012+）。
4.2 现象：EXEC(@sql)报错Invalid column name 'MerbarCode'，但表里明明有该字段
- 原因：
@OrderByColumn = 'MerbarCode'被直接拼入ORDER BY '+@OrderByColumn，若字段名含空格或特殊字符（如[Merch Bar Code]），未加括号会导致解析失败； - 解决：用
QUOTENAME(@OrderByColumn)包裹，生成ORDER BY [MerbarCode]；同理，所有动态字段名、表名都必须QUOTENAME。
4.3 现象：分页第 1 页正常，第 2 页返回空结果，且无报错
- 原因：
@CurPageFirstRow和@CurPageLastRow是int，但cast(@curpagefirstrow as varchar)在EXEC字符串中拼接时，若值为负数（如计算错误导致(pageNo-1)*pageSize +1 = -19），生成BETWEEN -19 AND 0，而ROW_NUMBER()从 1 开始，永远不匹配； - 解决：在拼接前校验参数有效性，
IF @CurPageFirstRow < 1 SET @CurPageFirstRow = 1；或改用OFFSET-FETCH，天然拒绝非法偏移。
4.4 现象：JSON 字符串末尾多出一个},，导致前端JSON.parse()失败
- 原因：
sourceTable拼接时以,"key":"val",格式追加，最后一字段后仍带,，而REPLACE(sourceTable, ',,', ',')仅处理双逗号，无法清除末尾单逗号；且sourceTable+''\"rn\":\"...\"},''显式加了},； - 解决：改用
STUFF函数精准删除末尾逗号，如STUFF(sourceTable, LEN(sourceTable), 1, '')；或重构为STRING_AGG（SQL Server 2017+）。
4.5 现象：查询大表（千万级）时 CPU 100%，持续 30 秒以上，sys.dm_exec_requests显示status = suspended
- 原因：
SELECT COUNT(...)子查询(select count('+@OrderByColumn+') from '+@TableName+' )在每行都执行一次，形成 N+1 查询；且未加索引提示，全表扫描； - 解决：将
total计算抽离为独立SELECT @total = COUNT(*) FROM ...，只执行一次；或用sys.dm_db_partition_stats查行数（近似但极快）。
5. 设计取舍：为什么宁可写 200 行 C# 序列化，也不在 SQL 里拼 JSON？
看到这里，你可能会问：既然问题这么多，为什么还有人用这种写法？答案不是“他们不懂”，而是在特定约束下，这是成本最低的短期解法。我曾在一个遗留系统改造项目中，面对三个硬约束：1）不能改应用层代码（Java WebService）；2）DBA 禁止开xp_cmdshell或 CLR 集成；3）SQL Server 版本锁死在 2008 R2（无FOR JSON）。当时我们评估了四条路：
| 方案 | 实现成本 | 运维成本 | 性能风险 | 兼容性 |
|---|---|---|---|---|
| 动态 SQL 拼接（本文脚本） | ★☆☆☆☆（1 天） | ★★★★☆（日志难查、参数难调试） | ★★★★☆（CPU/内存波动大） | ★★★★★（2005+ 全支持） |
升级 SQL Server 到 2016+ 用FOR JSON | ★★★★☆（2 周停机窗口） | ★☆☆☆☆（标准语法，Plan 可缓存） | ★☆☆☆☆（优化器原生支持） | ★★☆☆☆（需升级 DB） |
| 在应用层用 Jackson/FastJSON 反序列化 | ★★★☆☆（3 天改 Java） | ★☆☆☆☆（日志清晰、断点可控） | ★★☆☆☆（网络+GC 开销） | ★★★★☆（不依赖 DB 版本） |
| 写 CLR UDF 实现 JSON 序列化 | ★★★★★（5 天+安全审计） | ★★☆☆☆（需 DBA 开启 unsafe_assembly） | ★★☆☆☆（CLR 加载开销） | ★★★☆☆（需配置信任） |
最终选了方案一——不是因为它好，而是因为它是唯一能在 48 小时内上线、且不触发任何审批流程的方案。但上线后第三天，我们就启动了方案三的迁移。真正的技术决策，从来不是比“谁更正确”，而是比“谁的代价当前最可承受”。
这也解释了为什么FOR JSON在 SQL Server 2016 中被设计成这样：
SELECT * FROM Merchandise ORDER BY MerbarCode OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY FOR JSON PATH, ROOT('data'), INCLUDE_NULL_VALUES;PATH模式自动处理嵌套、数组、NULL 映射；ROOT('data')解决了本文脚本里手动拼{和}的脆弱性；INCLUDE_NULL_VALUES显式控制 null 字段是否输出，而非靠ISNULL(..., '')硬编码；- 最关键的是：
FOR JSON是查询计划的一部分，OFFSET-FETCH分页和 JSON 序列化共享同一执行树，避免了本文脚本中ROW_NUMBER()+COUNT(*)的双重扫描。
从那以后，我每次看到需要“SQL 生成 JSON”的需求，第一反应不再是打开 SSMS 写动态 SQL，而是打开SELECT @@VERSION确认版本，然后查文档确认FOR JSON支持情况。如果版本太低，我会在需求评审会上明确说：“我们可以用动态 SQL 快速上线，但必须同步排期升级 DB 或迁移序列化到应用层——因为技术债不是不还，只是换了个地方记账。”希望帮到你。
本文还有配套的精品资源，点击获取
