简介面向需要在SQL Server中直接生成JSON并交付前端或落库的数据库开发人员这份docx文档提供了一套基于动态SQL与ROW_NUMBER()分页的自动转换方案。内容覆盖JSON基础结构、利用SYS.SYSCOLUMNS系统视图动态拼接字段、WITH子句创建临时结果集、EXEC执行动态语句以及通过INSERT INTO将JSON存入数据表、使用AJAX接口供前端调用的完整链路。资源为单个docx文件体积约30KB携带方便正文附有可改表名、页码和排序列的SQL示例稍作调整即可复用到实际项目。当前已有1141人学习适合具备T-SQL基础、希望减少中间层序列化代码进而提升数据接口效率的工程师参考。1. SQL自动生成JSON一段脚本解决分页接口的字符串序列化很多做前后端分离的开发者遇到给表格接口返回数据时第一反应是JSON应该在应用层拼先把查询结果塞进DataTable再一层层遍历拼字符串最后序列化返回。这个思路本身没问题但数据量一旦上到几万行应用层逐行拼JSON既慢又容易超时尤其是在SQL Server 2008/2012这类老版本上没有FOR JSON子句可用只能靠T-SQL手工拼。我拆过这份「SQL自动生成JSON数据」资源它用SYS.SYSCOLUMNS动态取出表的所有列名把每一行数据拼成一段JSON字符串再用ROW_NUMBER配合分页上下界返回指定页附带total总数最后EXEC一把梭。适合老版本SQL Server环境下写报表、做表格接口的从业者也适合想搞懂动态SQL列名拼接的初学者。2. 源码拆解五个变量、SYS.SYSCOLUMNS与动态拼接的完整链路这段脚本的核心不是「生成JSON」这个动作而是怎么在一条动态SQL里同时完成四件事取列名、拼字段、分页、计数。原脚本大体分三段先声明变量再用SYS.SYSCOLUMNS累加列名最后补分页与总数并执行。下面按执行顺序拆开讲重点说清楚每个变量为什么这样设、拼出来的SQL长什么样、哪些地方在后续改造时最容易出问题。2.1 五个核心变量职责、类型与分页公式变量声明是整个脚本的入口。TableName varchar(50) 存表名sql varchar(3000) 存拼接结果CurPageFirstRow 和 CurPageLastRow 是分页上下界OrderByColumn varchar(20) 存排序字段。分页上下界的计算不是随手写的如果程序传入pageNo和pageSize那么第一行是 (pageNo-1)pageSize1最后一行是 pageNopageSize对应SQL里常见的 row_number between 区间写法。原脚本在拼接时把这两个数值cast成了varchar再代入动态SQL目的很简单——动态SQL编译时只能看到字符串局部变量不会自动传进去。这里有个容易被忽略的限制sql是varchar(3000)而一张十几列的表、列值稍长一点拼出来的JSON文本很容易超过3000字符。后面做存储过程时我会统一改成NVARCHAR(MAX)。另外OrderByColumn直接拼进ORDER BY子句意味着如果这个变量来自外部参数等于把排序关键字裸奔给了拼接文本属于注入的高危点这一点放在第4章展开。提示CurPageFirstRow和CurPageLastRow在拼接时必须cast成字符串不要直接写变量名进SQL文本否则执行时SQL Server会去解析一个不存在的局部变量直接报错。2.2 SYS.SYSCOLUMNS取列名JSON字段名的来源拼字段名是整段脚本里最绕的一环原脚本核心是这条累积赋值select sql isnull(sql, with paging as ( select replace({ ) name : isnull(cast( name as varchar), ) , from SYS.SYSCOLUMNS where ID OBJECT_ID(TableName)逻辑说明select sql sql 子串 from 系统视图 是SQL Server里一种特殊的「循环累加」写法。SYS.SYSCOLUMNS对表里每一列返回一行每一行都会执行一次右边的赋值。第一次执行时sql还是NULLisnull触发把 with paging as ( select replace({ 这个前缀塞进去此后每一行追加一段 JSON字段模板内容是 列名:列值表达式, 。等系统视图所有行都处理完sql里就有了全字段的拼接模板末尾带一个多余的逗号留给后面的replace去掉。参数说明isnull(cast( name as varchar), ) 里的cast没有写长度这是原脚本最隐蔽的坑。SQL Server对cast(... as varchar)不带长度时默认按varchar(30)处理字段值一旦超过30个字符就被静默截断JSON里只会出现前半截内容且完全不报错。另外isnull把所有NULL值统一换成了空字符串前端后续无法区分「字段真的为空」和「字段为NULL」数据语义有损失。还有一点列值如果本身带双引号这里也照样拼进去最终JSON很可能是非法的。2.3 分页与计数ROW_NUMBER、total与结尾拼接取完列名紧接着是第二段拼接把分页逻辑和总数接上去set sql sql ,, ) as sourceTable, ROW_NUMBER() OVER(ORDER BY OrderByColumn ) as rn, (select count( OrderByColumn ) from TableName ) as total from TableName ) select sourceTablern:cast(rn as varchar),total:cast(total as varchar)}, from paging where rn between cast(CurPageFirstRow as varchar) and cast(CurPageLastRow as varchar)逻辑说明这一段把之前拼好的列名模板完善成一个完整的CTEsourceTable就是第一段拼接出来的整行JSON片段rn来自ROW_NUMBER()窗口函数按OrderByColumn排序后给出行号total是标量子查询返回整个表的总行数。最外层select把rn和total追加到sourceTable后面拼成 rn:1,total:200}, 的尾部格式再通过 where rn between 限定了当前页区间。最后EXEC(sql)执行整段动态语句返回的就是一列sourceTable字符串。参数说明total在每一行都会重复返回这是为了方便前端分页条直接读取不用再发一次count请求。代价是数据冗余行数多的时候total字符串会重复传输但对分页接口来说通常可接受。rn和total都被cast成了varchar再接进JSON字符串前端拿到的都是字符串类型做数值运算前需要先parseInt这是和FOR JSON序列化最大的行为差异。2.4 为什么用ISNULL初始化变量累加技法的关键初学者最容易卡住的一个问题为什么不用 set sql with paging as... 然后再select累加原因是表可能只有一列也可能列数为零。如果先set再累加一旦系统视图返回零行select赋值语句根本不会执行sql停留在初始值拼出来的SQL是残缺的。而isnull写在select内部第一次循环时 sql 还是NULLisnull把前缀塞进去后续每次累加都是「已有内容新列」的追加等于把初始化动作藏进了循环本身。这是旧版SQL Server社区里常见的一种动态SQL拼法缺点是可读性差调试时必须单独打印最终sql才能看清拼出来的是什么。理解了这段逻辑再看EXEC(sql)为什么能拿到表名和排序值因为拼接阶段就已经把这些变量的值内联成字符串文本了。动态SQL执行时看到的是一段完全自包含的语句不依赖原来的局部变量作用域。反过来也说明所有从外部进来的值都会以纯文本身份参与编译才有了后面要说的注入和转义问题。3. 落地改造把脚本包成存储过程并接上分页与前端调用直接复制原脚本能用但生产环境没人愿意每张表改一堆变量赋值。我一般做两步改造第一步把整段逻辑包成存储过程把表名、页码、页大小和排序列提成参数第二步把varchar(3000)换成NVARCHAR(MAX)同时把分页上下界的计算挪到过程内部。下面给出经过整理的完整版本。3.1 包成存储过程参数化分页与结果集返回把原脚本整理成可直接创建的存储过程create procedure paging_json pageno int, pagesize int, tablename sysname, orderby sysname as begin set nocount on declare sql nvarchar(max) declare first int declare last int set first (pageno - 1) * pagesize 1 set last pageno * pagesize select sql isnull(sql, Nwith paging as ( select replace({ ) N name N: isnull(cast( name N as nvarchar(500)), N) N, from SYS.SYSCOLUMNS where ID OBJECT_ID(tablename) set sql sql N,, ) as sourceTable, NROW_NUMBER() OVER(ORDER BY orderby N) as rn, N(select count(*) from tablename N) as total Nfrom tablename N ) Nselect sourceTableNrn:cast(rn as nvarchar(20))N,total:cast(total as nvarchar(20))N}, N from paging where rn between cast(first as nvarchar(20)) N and cast(last as nvarchar(20)) exec (sql) end逻辑说明调用方只需传入页面、页大小、表名和排序列页上下界在过程内用公式算好。sql升级为nvarchar(max)列值cast也加了500长度先解决掉原脚本最大的截断隐患。exec (sql)直接返回结果集应用层拿到的还是sourceTable字符串列。参数说明orderby是拼接进ORDER BY的如果业务上排序字段可枚举建议在调用处先做白名单映射再传入pageno最小值为1pagesize建议限制上限比如500防止一次取出太多行把网络打满。返回的每一行JSON碎片都以逗号结尾应用层需要自行聚合成完整数组这一点和直接返回JSON数组的接口不同前端要适配。3.2 加WHERE条件让分页查询真正可用只有分页没有筛选接口基本没法用。在原脚本的基础上加where参数改造点只有一个在CTE的from子句之后拼上where片段。alter procedure paging_json pageno int, pagesize int, tablename sysname, orderby sysname, where nvarchar(500) N11 as begin -- ... 前面的列名拼接保持不变 ... set sql sql N,, ) as sourceTable, NROW_NUMBER() OVER(ORDER BY orderby N) as rn, N(select count(*) from tablename N) as total Nfrom tablename N where where N ) Nselect sourceTableNrn:cast(rn as nvarchar(20))N,total:cast(total as nvarchar(20))N}, N from paging where rn between cast(first as nvarchar(20)) N and cast(last as nvarchar(20)) exec (sql) end逻辑说明where默认值设为 11不传筛选条件时不影响原SQL语义传入时会插到表名和右括号之间使ROW_NUMBER和count都作用在筛选后的数据集上。row_number的排序和过滤的先后顺序是固定的先where过滤再排序编号最后between取页这一点保证了分页结果不会出现跨页重复或漏行。参数说明where虽然只是拼接字符串但它和orderby一样是注入高危口。推荐的做法是应用层只允许传白名单字段拼好的条件比如前端传price100后端映射成 column 固定字段名 再拼进字符串不要让用户原始输入直接进入动态SQL。总数字段用count(*)替代了原脚本的count(OrderByColumn)避免排序列出现NULL时计数结果异常。3.3 把JSON落库与前端表格调用原摘要里给了一个INSERT的示例实际落库可以这样用declare pageno int 1, pagesize int 20 declare tablename sysname NMerchandise declare orderby sysname NMerbarCode insert into JsonLog(TableName, JsonData, CreateTime) select tablename, sourceTable, getdate() from openquery(你的链接服务器, exec paging_json 1, 20, Merchandise, MerbarCode)逻辑说明如果只是想把每次分页结果留存审计可以直接把存储过程返回的sourceTable插入一张日志表。注意openquery只适合链接服务器场景同库直接用insert into...exec 存储过程即可。JsonData建议用nvarchar(max)CreateTime用datetime默认值方便按时间清理历史数据。前端调用环节我用最常见的jQuery写法演示重点是后端返回的字符串数组需要二次处理才能变成可用JSON$.ajax({ type: GET, url: /api/getJsonData?pageNo1pageSize20, dataType: json, success: function(res) { const jsonStr res.data.join(); const rows JSON.parse([ jsonStr.replace(/,\s*$/, ) ]); console.log(rows.length, rows[0].rn, rows[0].total); } });逻辑说明res.data是后端从存储过程结果集里读出的字符串数组每一行都是类似 {col1:v1,rn:1,total:23}, 的碎片。先用join把碎片拼成一个大字符串再包上中括号变成JSON数组replace负责去掉最后一个碎片末尾的逗号。这样JSON.parse就能得到正常数组。参数说明rows里的字段值全部是字符串rn字段需要parseInt之后才能用于页码状态显示total同理。如果希望后端直接返回合法JSON数组可以在存储过程最后用FOR XML PATH把所有碎片包一层但那样会改变结果集结构前端反而要改更多。4. 避坑与排查动态SQL拼JSON的五个翻车现场这段脚本我用过几个项目也帮别人排过不少错。下面五条是实际遇到过的坑按「现象 → 原因 → 解决」写清楚能帮你少走弯路。4.1 字符串被截断varchar(3000)装不下分页JSON现象返回的sourceTable列只有前半截中间断开JSON.parse直接报错有时是到某个字段后戛然而止看起来像SQL语句写错。原因sql原本是varchar(3000)列一多或者某个字段值稍微长一点拼接长度就超了。varchar超长时SQL Server不会报错而是把多余部分截掉结果就是一段残缺的JSON。解决把sql改成nvarchar(max)同时把值转换的cast也加上明确长度。我一般统一用 nvarchar(500)文本类字段就算比较长也够用真遇到大文本字段分页接口本来就不该把它完整拉出来。4.2 cast默认长度30字段值被无声截断现象某个列的值明显超过30字符接口返回后数据丢了后半截而且没有任何SQL报错排查半天以为是程序问题。原因isnull(cast( name as varchar), ) 里cast没指定长度SQL Server对varchar不写长度时默认是30。这是最隐蔽的雷因为它发生在动态SQL内部SSMS里单独执行还看不出问题只有拼接后的最终SQL才会暴露。解决改成 nvarchar(500) 或 nvarchar(max)。顺带提醒凡是cast成varchar做拼接的地方都养成写长度的习惯这个坑不止出现在这份脚本里在任何动态SQL拼JSON的场景都会遇到。4.3 单引号转义值里一个撇号毁掉整条动态SQL现象某条数据的某个字段值是 OBrien 这类带英文撇号的文本执行存储过程时直接报语法错误或者生成出的SQL半截断掉。原因字段值被cast成字符串后内联进了动态SQL单引号把外层字符串边界截断了最终SQL文本的引号配对全部错乱。这和SQL注入的原理一模一样只是这里是无意的。解决拼接前把值里的单引号替换成双单引号。比如在cast表达式外面再套一层 replace(cast(列名 as nvarchar(500)), , )把每个单引号加倍这是T-SQL里的标准转义方式。如果整个动态SQL改用sp_executesql参数化值参数可以避免转义但列名仍然无法参数化所以转义这步省不掉。4.4 排序列直接拼接外部参数注入点和语法炸弹现象调用存储过程时传入的排序列名带了个分号或者拼了一段特殊SQL结果执行报错更麻烦的是有人传 order by 1; drop table xxx 之类的文本直接威胁表结构。原因OrderByColumn原样拼进 ROW_NUMBER() OVER(ORDER BY ...) 里没有做任何过滤。排序字段不像表名那样有OBJECT_ID校验写什么拼什么。解决在存储过程内部对orderby做白名单映射。我一般这么处理先用一个表值参数或者switch case把允许排序的字段名固定成一个集合外部传进来的值先用字典映射成白名单内的物理列名匹配不上就返回错误。QUOTENAME只能防字段名意外的字符防不了「被允许的列名批量传入」白名单才是最稳妥的。4.5 NULL与空字符串混为一谈日期浮点同步失真现象前端表格里出现大量空字符串明明数据库里是NULL日期字段显示成 2024-01-01 00:00:00 这种默认格式浮点数字偶尔变成科学计数法前端拿到后显示成一串奇怪的字符。原因isnull把所有NULL换成了空字符串丢失了NULL与空串的语义差异cast日期和浮点数到varchar时SQL Server按会话默认格式转换日期带了时间、浮点走科学计数法JSON里的值就变成了不好解析的形态。解决把NULL输出成JSON的null字面量日期先convert成明确格式再拼浮点用str函数控制小数位。改造示例case when 列名 is null then null else replace(cast(列名 as nvarchar(500)), , \) end逻辑说明这个case把NULL输出为无引号的null符合JSON标准非NULL值仍然带双引号输出为字符串。replace把字段内容里的双引号转义成 避免JSON结构被破坏。日期字段可以在cast前加一层convert(nvarchar(10), 列名, 120)只保留日期部分浮点字段用 cast(cast(列名 as decimal(18,2)) as nvarchar(20)) 控制小数位。参数说明这套方案只适用于确定要字符串化的值。如果前端需要数值类型做计算就不要统一输出字符串可以单独把数值列走一条分支输出成不带引号的数字字面量但脚本复杂度会明显上升一般分页列表场景字符串够用。5. 验证与进阶把JSON结果从能出到好用存储过程改完、接口能返回JSON不是终点。我每次写完这类动态SQL都会多做三步验证能挡掉大多数隐藏问题。第一步是验证每一行结果是不是合法JSON。SQL Server 2016以上可以直接用ISJSON函数select isjson(replace(sourceTable, ,, )) as json_ok from paging老版本没有ISJSON可以在应用层里把整个结果集join后包上中括号用JavaScript的JSON.parse验证解析失败就把原始字符串打印出来定位到具体行。我习惯在调试存储过程时把最终sql打出来看一眼比直接在结果集里猜快得多——很多引号问题一眼就能在拼好的全文里发现。第二步是观察动态SQL对执行计划缓存的影响。EXEC(sql)每执行一次SQL Server都会生成一份独立的执行计划如果表名和排序列经常变计划缓存里会堆满碎片。缓解办法是把稳定参数改成sp_executesql传参比如first和last可以用参数传入但表名、列名这类对象名依旧无法参数化只能靠白名单限死变化范围。第三步是输出格式的耐久性改造。原脚本把所有值统一转成字符串短期够用但前端做排序和范围筛选时会很难受。进阶一点的做法是给脚本加一个outputType参数传verbose就输出字符串片段传compact就让数值字段不带引号输出。实测中这类改造能让接口在分页列表和导出场景之间复用不用写两套存储过程。最后一个习惯帮我把很多线上问题拦在了测试阶段从那以后每次拼完动态SQL生成JSON我都会强制走一遍「长度检查 引号配对检查 ISJSON验证」三步再交给前端联调。动态SQL拼接这类代码运行时才报错的概率很高前置检查做足了后面能省下大量排查时间。希望帮到你。本文还有配套的精品资源点击获取