简介面向SQL Server开发者与数据库管理人员的技术笔记阐述如何利用SQL语言自动生成JSON数据并将分页结果通过接口供前端调用或落地写入数据表。文档从JSON键值对结构讲起重点演示基于WITH子句与ROW_NUMBER()进行分页查询的完整思路先声明表名、当前页首行/末行、排序列等变量再借助SYS.SYSCOLUMNS系统视图按表动态读取列名通过ISNULL将空值转为空字符串并逐列拼接成JSON格式最后由EXEC执行动态SQL返回结果。资源本身为1个docx文档文件大小约30KB内容精炼紧凑既给出了可直接套用的SQL代码片段也补充了INSERT语句将JSON存储到数据表以及AJAX请求调用API的示例方便迁移到真实业务场景。目前已有1143人学习适合掌握SQL基础、希望在数据库层快速完成数据格式化的中高级开发者做接口对接或报表输出时可作参考。文档结构清晰适合作为日常开发的速查手册。1. SQL自动生成JSON数据在数据库端直接拼字符串的取舍接了个老系统维护的活前端表格要分页JSON可后端还是SQL Server 2008R2连个像样的框架都没有数据库账号还只给读权限。查一次列表要等两三秒前端催得急我只能在存储过程里想办法用SQL自动生成JSON数据把分页结果直接拼成JSON字符串返回。这份资源的核心就是那段利用SYS.SYSCOLUMNS遍历表结构、动态拼接JSON并带ROW_NUMBER分页的脚本。它适合还在维护老库、不想动服务端代码的开发者也适合想搞懂动态SQL拼接到底怎么不翻车的朋友。这方案不优雅但在特定环境里真的能救命。2. 拆解核心脚本变量职责、系统列遍历与单引号嵌套这段脚本第一眼确实劝退满屏单引号像乱码。但拆开看逻辑并不复杂先声明一批变量再用系统视图把表的所有列名取出来逐个拼成JSON键值对片段最后套一个分页CTE执行。难点全在字符串嵌套上。2.1 五个变量各管什么原脚本开头声明了五个变量各自职责很明确我用一张表把它说清楚变量名类型作用赋值要点TableNamevarchar(50)要查询的表名直接赋字符串注意别带库名和架构前缀带了会出问题sqlvarchar(3000)动态SQL的拼接载体声明时不要赋值让它保持NULL后面用ISNULL做首段初始化CurPageFirstRowint当前页第一行公式是 (pageNo-1)*pageSize 1由程序算好传进来CurPageLastRowint当前页最后一行公式是 pageNo*pageSize同样由程序算好OrderByColumnvarchar(20)分页排序列必须是表里真实存在的列否则ROW_NUMBER会直接报错赋值公式在原脚本注释里写得很清楚这五个变量就是整个方案的外部接口。要注意的是OrderByColumn长度只有20如果表里列名超过20个字符赋值就会截断。这个我后面在第5章避坑里会细说。2.2 动态SQL最终长什么样理解这段脚本最好的办法是先看它最终生成并交给EXEC执行的那段SQL是什么样。假设表结构里有MerbarCode、MerName、MerPrice三列动态拼接完成后实际执行的是下面这段WITH paging AS ( SELECT { MerbarCode: ISNULL(CAST(MerbarCode AS varchar), ) , MerName: ISNULL(CAST(MerName AS varchar), ) , MerPrice: ISNULL(CAST(MerPrice AS varchar), ) , AS sourceText, ROW_NUMBER() OVER (ORDER BY MerbarCode) AS rn, (SELECT COUNT(MerbarCode) FROM Merchandise) AS total FROM Merchandise ) SELECT sourceText rn: CAST(rn AS varchar) , total: CAST(total AS varchar) } FROM paging WHERE rn BETWEEN 1 AND 20;每一行查询结果都是一段形如{MerbarCode:001,MerName:夹克,MerPrice:12.50,rn:1,total:100}的完整JSON对象字符串。sourceText字段负责拼业务列rn是行号total是表总行数外层WHERE用rn做范围过滤实现分页。这里有个关键设计每行输出是一个独立的JSON对象多行之间并没有逗号连接。前端拿到这个结果集后要么自己遍历拼数组要么在程序端用逗号JOIN成数组。原脚本的特点是只负责生成对象串不负责生成数组串这个边界要搞清楚不然前端会踩坑。2.3 用SELECT变量累加遍历列名动态SQL的核心在拿到表里所有列名。原脚本用的是SYS.SYSCOLUMNS这个系统视图配合SELECT变量累加的写法SELECT sql ISNULL(sql, with paging as ( select replace({ ) name:isnull(cast(name as varchar),), FROM SYS.SYSCOLUMNS WHERE ID OBJECT_ID(TableName) ORDER BY colid;这段代码的逻辑是从系统视图逐行读取列名每读一列就往sql后面追加一段JSON键值对的模板。注意这不是普通的结果集查询而是SQL Server特有的变量累加——结果集有多少行赋值语句就执行多少次。ISNULL(sql, ...)的作用是当sql还是NULL时先用固定字符串做初始化之后每一轮都在已有基础上追加。ORDER BY colid是必须的colid是列在表里的物理顺序不加这个排序拼接出来的字段顺序不稳定前端字段错乱就是这种玄学问题。2.4 嵌套单引号为什么容易翻车SQL Server字符串里表示一个单引号要写成两个单引号这段脚本把动态SQL和JSON的引号叠在一起等于三重嵌套。比如name:这半段拆开看是第一个代表一个普通单引号随后是JSON的双引号中间的name是动态列名。也就是说它拼出来的是列名:但人眼很难一眼辨认。我一般会用一个小技巧验证嵌套对不对把最终生成的sql用PRINT打出来而不是直接EXEC。PRINT能让你看到真正要执行的SQL长什么样单引号对不对、位置错没错一眼就清楚。这也是我后面封装存储过程时保留的调试习惯。原脚本这段单引号嵌套其实藏着一个替replace吞逗号的隐患我在第5章避坑部分单独讲。3. 参数化改造把固定脚本封装成可复用的分页JSON存储过程原脚本是一次性脚本表名写死、分页参数写死每次换表都要改代码。实际项目里这样用不现实我一般会把它封装成存储过程把表名、页码、每页条数、排序列都变成入参再加一个输出参数直接返回拼好的JSON数组。这样Java、C#或是前端Node层都只需要调一个存储过程拿到的直接是能用的字符串。3.1 为什么必须做参数化和白名单校验直接让外部传表名和排序列进去拼动态SQL是典型的注入入口。表名被拼进FROM子句列名被拼进ORDER BY这两个位置一旦能注入整个库就裸奔了。常见做法是两层防御第一层用QUOTENAME把表名和列名用方括号包起来第二层用sys.columns校验排序列是否真实存在于目标表不存在就直接报错返回不给任何拼接机会。3.2 完整的存储过程封装脚本下面是我整理后的版本兼容SQL Server 2008R2没有用任何高版本特性CREATE PROCEDURE dbo.usp_GetPagedJson TableName NVARCHAR(128), PageNo INT, PageSize INT, OrderByColumn NVARCHAR(128), JsonResult NVARCHAR(MAX) OUTPUT AS BEGIN SET NOCOUNT ON; DECLARE sql NVARCHAR(MAX) N; DECLARE colFragment NVARCHAR(MAX) N; DECLARE FirstRow INT (PageNo - 1) * PageSize 1; DECLARE LastRow INT PageNo * PageSize; -- 1. 入参基础校验 IF PageNo 1 OR PageSize 1 BEGIN RAISERROR(PageNo 和 PageSize 必须大于 0, 16, 1); RETURN; END; -- 2. 安全化处理表名和排序列都包上方括号 SET TableName QUOTENAME(TableName); SET OrderByColumn QUOTENAME(OrderByColumn); -- 3. 校验排序列确实存在于目标表 IF NOT EXISTS ( SELECT 1 FROM sys.columns WHERE object_id OBJECT_ID(TableName) AND name OrderByColumn ) BEGIN RAISERROR(OrderByColumn 不在目标表中请检查列名, 16, 1); RETURN; END; -- 4. 遍历系统列拼JSON键值对片段 SELECT colFragment colFragment N c.name N: ISNULL(CONVERT(varchar, QUOTENAME(c.name) N), ) , FROM sys.columns c WHERE c.object_id OBJECT_ID(TableName) ORDER BY c.column_id; IF colFragment IS NULL BEGIN RAISERROR(找不到表或表中没有可用列, 16, 1); RETURN; END; -- 5. 去掉末尾多余的逗号否则JSON会多一个逗号报错 SET colFragment LEFT(colFragment, LEN(colFragment) - 1); -- 6. 组装最终动态SQL SET sql NWITH paging AS ( NSELECT { colFragment N AS sourceText, NROW_NUMBER() OVER (ORDER BY OrderByColumn N) AS rn, N(SELECT COUNT(*) FROM TableName N) AS total NFROM TableName N ) NSELECT sourceText rn: CAST(rn AS varchar) ,total: CAST(total AS varchar) } NFROM paging NWHERE rn BETWEEN CAST(FirstRow AS varchar) N AND CAST(LastRow AS varchar); -- 7. 执行动态SQL逐行收集结果 DECLARE lines TABLE (line NVARCHAR(MAX)); INSERT INTO lines (line) EXEC(sql); -- 8. 把多行JSON对象拼成合法数组 SELECT JsonResult ISNULL(JsonResult N,, N[) line FROM lines; SET JsonResult JsonResult N]; END; GO这段代码的逻辑步骤我拆一下第1步做入参校验页码小于1直接拒绝第2步QUOTENAME把表名列名包成[表名]、[列名]这能挡住大部分注入第3步校验排序列存在性避免拼出个非法列名让SQL在运行时才报错第4步是核心循环拼所有列的JSON片段注意这里我直接用了CONVERT(varchar, ...)而没有用CAST因为后面避坑章节要讲日期格式问题第5步用LEFT去掉最后一个逗号这是替代原脚本replace方案的关键改动第6步组装完整动态SQL第7、8步把每行JSON对象用逗号连接并包上方括号生成完整的JSON数组返回。3.3 存储过程调用方式与参数边界封装好之后外部调用非常简单DECLARE out NVARCHAR(MAX); EXEC dbo.usp_GetPagedJson TableName Merchandise, PageNo 1, PageSize 20, OrderByColumn MerbarCode, JsonResult out OUTPUT; SELECT out;参数边界有几个要注意的地方。TableName不建议传多段名称比如dbo.MerchandiseQUOTENAME会把整个字符串包成[dbo.Merchandise]反而让SQL Server找不到对象。PageSize不建议超过500拼出来的JSON字符串会非常大NVARCHAR(MAX)虽然能撑住但网络传输和前端解析都会变慢。OrderByColumn必须是普通列名不建议传MerbarCode DESC这种带排序方向的值QUOTENAME会把空格也包进去排序方向应该在存储过程外面再拼接一个方向参数才稳妥。3.4 为什么不用EXEC而用sp_executesql原脚本用的是EXEC(sql)我封装时保留了EXEC但生产环境我会建议换sp_executesql。差别在于EXEC每次执行都生成新的执行计划缓存得不到复用sp_executesql支持参数化首次执行后执行计划能缓存下来。对这个场景影响大的是分页值不同会导致整个SQL字符串不同EXEC的方式等于每次都是新SQL无法命中缓存。EXEC sp_executesql sql, NFirstRow int, LastRow int, FirstRow, LastRow;注意用sp_executesql时分页的两个参数用占位符传不要直接拼进字符串。排序列和表名因为用了QUOTENAME本身已经安全可以留在字符串里。4. 落地到项目前端AJAX调用、JSON落库与接口返回结构脚本能跑通只是第一步真正把它接入项目要处理前端怎么拿、数据怎么存、接口返回什么结构三个问题。这三个问题处理不好后端拼得再漂亮前端一样白屏。4.1 前端AJAX拉取与JSON.parse的转义坑假设你把存储过程包进了一个API接口接口返回的就是存储过程拼好的字符串。前端用AJAX拉取时最容易翻车的是返回的是字符串还是对象搞不清楚。我处理这类老接口的习惯是接口层不替前端解析原样返回字符串前端自己判断类型再解析。fetch(/api/getPagedJson?tableMerchandisepage1size20) .then(res res.json()) // 这里拿到的可能已经是对象 .then(data { // 老接口经常字符串套字符串多一层parse才安全 const rows typeof data string ? JSON.parse(data) : data; rows.forEach(row { console.log(row.MerbarCode, row.rn, row.total); }); });这个代码块的逻辑说明第一层res.json()把HTTP响应体解析成JS值但如果接口返回的Content-Type是text/plain这里拿到的就是一个字符串字符串里面装的是JSON数组文本。所以第二层做了类型判断是字符串就再parse一次。这种字符串套JSON的接口在老系统里特别常见因为后端存储过程出来的就是字符串接口层偷懒没转换。真正难搞的是数据本身带引号。比如商品名称是夹克24款SQL拼接时直接生成MerName:夹克24款JSON结构直接破损。修复要在SQL端做把字段值里的双引号转义成\前端看到的就是合法的JSON了。-- 在拼JSON片段时对字符串类型字段做转义处理 REPLACE(ISNULL(CONVERT(varchar, MerName), ), , \)这个转义必须放在ISNULL外层因为NULL值不需要转义先处理NULL再替换引号顺序不要反。4.2 把JSON结果落库两种做法与适用场景摘要里提到可以用INSERT INTO把JSON存到数据表。这个用法有两种理解一是把整个JSON字符串存到一个字段里二是在存储过程中用INSERT INTO把动态查询结果落到临时表再加工。原摘要给的例子是第一种INSERT INTO MyTable (JsonData) SELECT JsonResult; -- 变量里存的是完整JSON字符串这种方式适合做接口缓存比如某张表的JSON不常变每天凌晨批量生成一次存到缓存表前端直接查缓存表避免每次都跑动态SQL。第二种方式在存储过程内部更常见把EXEC的结果先落进表变量或临时表再对这个结果集做二次加工比如补字段、排序、去重。我封装版本里用的DECLARE lines TABLE(...)就是这种思路。落库时有个隐藏问题NVARCHAR(MAX)字段存的JSON字符串如果超过8000字符在SQL Server里和普通字符串拼接时会有隐式截断风险。我一般会把相关变量都声明成NVARCHAR(MAX)并且拼接时确保至少一个操作数是MAX类型就不会触发截断。4.3 接口返回结构设计对象数组还是带分页元数据存储过程返回的JSON数组长这样[ {MerbarCode:001,MerName:夹克,rn:1,total:100}, {MerbarCode:002,MerName:卫衣,rn:2,total:100} ]注意total字段被放在了每一行里而不是作为独立的分页元数据。这说明原脚本的设计意图是让前端在每一行都能拿到总行数省一次额外查询。但如果total每次都重复一百行数据就重复传了一百次浪费带宽。我通常会在存储过程外面再包一层把元数据和数据分开{total: 100, rows: [ {MerbarCode:001,MerName:夹克}, {MerbarCode:002,MerName:卫衣} ]}做法很简单存储过程只负责拼业务数据行不拼total接口层再单独查一次COUNT拼到外层。代价是多一次COUNT查询但换来的是结构更干净、前端不用从每行里剥total。4.4 性能观察动态SQL的编译开销在哪这个方案最耗时的不是拼接本身而是每次执行动态SQL时的编译开销。表结构越复杂、列越多SYS.SYSCOLUMNS遍历和字符串拼接耗时越长。我在一个200列的大宽表上测试过单次拼接耗时在几十毫秒量级这在和前端交互时几乎感知不到但如果是高频调用比如每秒几十次这个开销会累积。另一个性能观察点是EXEC和sp_executesql的差异。实测下来参数化后的存储过程在第二次调用时执行计划能命中缓存整体响应时间能下降30%左右。如果接口是高频路径这个优化值得做。如果是低频后台任务EXEC的写法简单直接也够用。5. 避坑排查动态拼接SQL最常见的五个翻车点这段脚本我在两个项目里用过自己也踩过不止一次坑。把最常见的五个问题按现象、原因、解决的顺序写出来你遇到的时候直接对照排查。5.1 JSON里NULL变成了空字符串前端判空失效现象接口返回的JSON里某些字段的值是但数据库里明明是NULL。前端用if (row.MerName null)判断永远不成立页面显示出空字符串而不是暂无。原因原脚本用了ISNULL(CAST(col AS varchar), )把NULL强制换成了空字符串。JSON标准里NULL应该写成null不带引号而空字符串是两个完全不同的语义。解决按字段语义选择处理方式。如果前端需要区分NULL和空串改成CASE表达式CASE WHEN col IS NULL THEN null ELSE REPLACE(CONVERT(varchar, col), , \) END注意null在JSON里不能加引号而其他值要加引号并转义内部引号。改完之后前端能正常识别NULL但SQL拼接复杂度也上去了属于小表可以接受、大宽表会比较拖慢拼接速度的方案。5.2 单引号嵌套乱套replace把字段分隔逗号全吞了现象执行EXEC时报语法错误或者返回的JSON字段之间没有逗号分隔所有字段值串成一团。PRINT输出动态SQL发现怪异的字符排列。原因原脚本的闭合逻辑用了replace({ ... , ,, })意图是把行首的{后面的最后一个逗号替换成}来闭合JSON对象。但replace作用于整个字符串会把前面所有字段之间的分隔逗号一并替换成}结果就变成每个字段值后面都跟一个}JSON直接坏掉。这个坑非常隐蔽因为它不是必现的——列数少的时候碰巧还能用列一多必翻车。解决放弃replace方案改成两步走。第一步拼接时每列末尾统一加逗号第二步用LEFT函数把最后一个逗号去掉再闭合SET colFragment LEFT(colFragment, LEN(colFragment) - 1);这样只删最后一个字符不碰中间任何逗号逻辑清晰可预测。我在第3章的存储过程封装里就用的这个方案。5.3 varchar(3000)截断JSON被切成半截现象返回结果在某个位置突然断了字段值只显示一半而且表里数据量越大断得越早。原因原脚本sql声明为varchar(3000)拼接的JSON模板加表结构信息很容易超过3000字符。SQL Server对超长字符串会静默截断不会报错导致动态SQL本身就不完整执行结果自然残缺。解决所有参与拼接字符串的变量全部升级为NVARCHAR(MAX)。不只是sql我在封装时把colFragment也声明成了NVARCHAR(MAX)。注意如果拼接过程中有一个中间变量还是varchar(3000)结果一样会被截断所以变量类型检查要全覆盖。5.4 bit类型输出0/1、日期时间带.000后缀现象JSON里性别字段输出0或1前端要的是male或female日期字段输出2024-01-01 00:00:00.000表格显示冗长难看。原因CAST无条件转了varcharbit类型转为数字字符串datetime类型转为带毫秒的完整格式都没有做格式化映射。解决在拼接字段片段前对特殊类型做单独映射。我用sys.columns查数据类型再做分支CASE WHEN c.system_type_id 104 THEN NCASE WHEN QUOTENAME(c.name) N 1 THEN true ELSE false END WHEN c.system_type_id IN (61, 40) THEN NCONVERT(varchar, QUOTENAME(c.name) N, 120) ELSE NCONVERT(varchar, QUOTENAME(c.name) N) END104是bit的type_id61和40是datetime和date。这样拼出来的片段生成JSON时格式就是可控的。这个判断是动态SQL里最常见的类型分派写法比每个表单独写死要通用得多。5.5 跨库执行时OBJECT_ID匹配不到表现象脚本单独在某个库执行正常但放到另一个库或者通过程序连接执行时报对象名无效或返回空数据。原因OBJECT_ID(TableName)不指定数据库和架构时以当前连接的默认Schema为准。如果连接串指定的库和应用实际访问的表不在同一个库里或者表属于某个自定义Schema而非dboOBJECT_ID就找不到对象。这在动态SQL统一拼写时特别隐蔽因为报错信息不直接指向OBJECT_ID这一行。解决在存储过程内部强制带上架构前缀SET TableName QUOTENAME(SCHEMA_NAME(schema_id)) . TableName;或者更简单调用方传参时统一要求带架构名。但前面说过QUOTENAME会处理多段名称要先用PARSENAME拆开再分别加括号不然dbo.Merchandise会被整个包起来。我一般建议接口层统一约定只传表名存储过程内部默认dboSET TableName Ndbo. QUOTENAME(TableName);这样跨库调用时只要连接的上下文有对应库就可以稳定工作。6. 进阶技巧用内置JSON函数验证输出并与FOR JSON PATH对比手工拼JSON最大的问题就是你以为对了实际没对。我后来养成一个习惯任何动态拼JSON的脚本上线前都要过一遍ISJSON验证。SQL Server 2016以上版本可以直接用内置JSON函数但即使你还在2008R2上也有办法验证——把EXEC结果先塞进表变量逐行检查格式。DECLARE lines TABLE (line NVARCHAR(MAX)); INSERT INTO lines (line) EXEC(sql); -- 找出所有不能通过JSON合法性校验的行 SELECT line, ISJSON(line) AS isValid FROM lines WHERE ISJSON(line) 0;这里ISJSON在2016以上会逐个验证JSON格式返回1是合法、0是不合法。对于老版本可以退而求其次检查字符串是否以{开头、以}结尾。这个方法便宜有效每次改完拼接逻辑后跑一遍能拦住八成以上的低级错误。我从那次把逗号吞了之后就强制自己每个加引号的改动都过一遍这个验证脚本。如果你的SQL Server版本在2016以上其实有更标准的做法就是内置的FOR JSON PATH。这个方案能替代手工拼接SELECT TOP 20 MerbarCode, MerName, MerPrice, ROW_NUMBER() OVER (ORDER BY MerbarCode) AS rn FROM Merchandise ORDER BY MerbarCode OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;FOR JSON PATH的优点是引擎自己处理引号转义、NULL值、日期格式不用手工拼也不会有单引号嵌套的翻车点。但它有两个硬边界一是要求SQL Server 2016及以上老库用不了二是它的输出格式是固定的你想做出{total:100,rows:[...]}这种自定义结构还是得在外面再包一层。所以我的建议是新库优先用FOR JSON PATH老库维护场景保留手工拼接方案两者之间用我在第3章封装好的存储过程做切换业务层无感。最后的习惯是每次改动这些拼接脚本我都强制走一遍三段检查PRINT出动态SQL看结构、ISJSON校验所有输出行、和FOR JSON PATH的结果做一次字段比对。一个字段漏传、一个引号没转义前端报表就是整列空白。这行黑匣子代码值得你花十分钟验证希望帮到你。本文还有配套的精品资源点击获取