拓冰网站定制 
简介面向SQL开发人员与数据处理初学者这份技术笔记围绕“用SQL自动生成JSON数据”展开解决将关系型查询结果转换为前端可调用或可持久化JSON的常见问题。文档为docx格式共1个文件压缩包约30KB内容精炼实用。目前已有1141人学习下载被广泛用于日常开发参考。笔记以SQL Server为例完整演示了声明TableName、sql、CurPageFirstRow等变量通过SYS.SYSCOLUMNS系统视图动态获取表结构利用ISNULL与字符串拼接构造查询语句结合ROW_NUMBER()实现分页排序最终由EXEC执行并返回JSON字符串的全过程同时给出将JSON写入数据表的INSERT语句以及前端通过AJAX调用接口获取数据的示例。读者可从中掌握动态SQL构建思路与JSON格式化技巧直接迁移到分页接口或数据交换场景中。1. 用 T-SQL 手工拼 JSON这套分页脚本到底在做什么先说结论在不引入 FOR JSON、不升级到 SQL Server 2016 的前提下纯靠 T-SQL 字符串拼接一样能生成前端能直接用的 JSON 数据。而且这套脚本把“分页查询”和“JSON 序列化”合并成了一次动态 SQL 执行一条语句同时返回当前页数据和总条数接口层连二次查询 count 都省了。我拆这份资源的时候第一反应是写法有点野但思路确实老练——它用 SYS.SYSCOLUMNS 动态取列名用 ROW_NUMBER() 做分页再用 replace 把行记录拼成{字段:值}的格式最后 EXEC 一把梭输出。适合的读者是那些还在用 SQL Server 2008R2/2012、前端又急着要 JSON 接口的老项目维护者新手拿它理解动态 SQL 和分页原理也很划算但直接用之前得先知道它有哪些边界坑。2. 拆解脚本的四个核心部件变量、列名采集、JSON 拼接、分页与执行2.1 变量清单每个变量是干什么的初始化时要注意什么这套脚本最先映入眼帘的是五个 declare 变量它们的分工非常明确TableName目标表名唯一需要你手填的业务参数。sql拼接用的动态 SQL 字符串脚本里特意注释“不要给它赋值”原因是后面要用isnull(sql, ...)做首次初始化如果你提前给了空字符串isnull 就不会走默认分支整个 with paging 的头部就拼不出来了。CurPageFirstRow 和 CurPageLastRow当前页的数据行区间前者是(pageNo-1)*pageSize 1后者是pageNo*pageSize也就是 1 到 20 代表第一页。OrderByColumn排序列名用于 ROW_NUMBER() 的 OVER 子句决定分页的稳定性。这套变量设计最值得借鉴的是把「表名」「排序列」参数化意味着同一个模板可以套到任意表上不用为每个表各写一份分页 SQL。它把分页边界值的计算责任交给了调用方通常是程序里算好了再传进来存储过程本身不做乘法运算这在老项目里是很常见的分工方式。2.2 SYS.SYSCOLUMNS 动态取列为什么用它而不是 INFORMATION_SCHEMA脚本里采集列名的核心语句是SELECT sql ISNULL(sql, with paging as ( select replace({ ) name:isnull(cast(name as varchar),), FROM SYS.SYSCOLUMNS WHERE ID OBJECT_ID(TableName)这段的逻辑是遍历目标表的每一列把列名 name 嵌进 JSON 键的位置同时生成一段取值表达式isnull(cast(列名 as varchar),)两段拼在一起形成列名:列值,这样一小节 JSON 片段。每一列都追加一次最终 sql 里就攒出了完整的行记录模板。这里有两个细节值得说。第一SYS.SYSCOLUMNS 是老兼容视图SQL Server 2000 时代就在用2005 之后官方更推荐 sys.columns但老系统里前者依然可用这套脚本明显是给老库准备的。第二isnull(cast(... as varchar),) 处理了 NULL 值和数值类型转换两个问题——NULL 输出为空字符串数字不会带上科学计数法之类的怪格式。这个处理思路在手工拼 JSON 时是刚需。2.3 replace 拼接 JSON它到底 replace 了什么为什么能拼出行记录脚本里有一行很绕的写法replace({ ... ,)。初学者容易懵我来拆一下。它的目的是每一行数据要拼成{字段1:值1,字段2:值2},这种片段但字段值是动态的所以模板里先写死{然后把“字段名 取值表达式 逗号”的片段循环拼进去最后用一个 replace 把字符串里代表单引号的占位符还原成真实单引号避免字符串嵌套把 T-SQL 搞断。从执行效果上说select 出来的每一行经过这段表达式后就是一个 JSON 对象字符串多个对象的逗号已经在字段片段里带上了后面再统一拼接行尾的 rn 和 total就形成了 JSON 数组的雏形。如果你自己写过类似脚本多半会遇到单引号转义把眼睛看花的问题这套写法用一种比较巧的占位符套路绕过去了。2.4 ROW_NUMBER 分页与 EXEC 执行分页边界怎么算结果怎么返回SELECT 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) EXEC(sql)这一段干了三件事用 ROW_NUMBER() OVER (ORDER BY 排序列) 生成行号 rn同时用标量子查询算出表的总行数 total两个值都会拼进每一行的 JSON 尾巴。外层 where 用 rn between 第一行 and 最后一行 截出当前页数据这是最常见的高效分页写法索引命中情况和 OFFSET 相比在老版本里更可控。EXEC(sql) 动态执行拼好的语句把结果集直接返回给调用方也就是你在 SSMS 里能看到的一列“JSON 文本”。还有一个容易漏掉的点脚本末尾拼接了rn:页码,total:总数},这段尾巴相当于每一行边返回数据边带上自己的行号和总量前端拿到后不用再发一次请求查总数。这在老接口里是很实用的小优化。变量用途初始化注意TableName目标表名必须存在否则 OBJECT_ID 返回 NULL取不到列sql动态 SQL 字符串保持 NULL用 isnull 触发首次初始化CurPageFirstRow当前页起始行号需要调用方预先算好如 (pageNo-1)*pageSize1CurPageLastRow当前页结束行号调用方预先算好如 pageNo*pageSizeOrderByColumn排序列名建议有唯一索引否则分页可能不稳定3. 把这套脚本接到业务里存储过程封装、参数传入、调用与输出确认3.1 封装成可复用的存储过程原脚本是裸的批处理生产环境直接跑没问题但接口层调用不方便。我一般会再加一层壳把它改成存储过程把表名、页码、页大小、排序列都变成入参这样程序里只要一条 EXEC 就能拿到 JSON 文本。下面是完整模板CREATE PROCEDURE proc_GetTableJsonData TableName NVARCHAR(50), PageNo INT, PageSize INT, OrderByColumn NVARCHAR(20) AS BEGIN SET NOCOUNT ON; DECLARE sql AS VARCHAR(3000); DECLARE CurPageFirstRow INT; DECLARE CurPageLastRow INT; SET CurPageFirstRow (PageNo - 1) * PageSize 1; SET CurPageLastRow PageNo * PageSize; -- 先构造 with paging 头部再用列名循环拼字段 JSON 片段 SELECT sql ISNULL(sql, with paging as ( select replace({ ) name:isnull(cast(name as varchar),), FROM SYS.SYSCOLUMNS WHERE ID OBJECT_ID(TableName); -- 补全 paging 查询体行号、总数、数据来源 SET sql sql ,,) as sourceTable, ROW_NUMBER() OVER(ORDER BY OrderByColumn ) as rn, (SELECT COUNT( OrderByColumn ) FROM TableName ) as total FROM TableName ) ; -- 外层拼分页条件和 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); EXEC(sql); END;这里我做了两个改动一是把分页边界值的计算移到过程内部程序调用时只需要传 pageNo 和 pageSize省得每回在代码里手算二是加了 SET NOCOUNT ON避免 DONE_IN_PROC 消息干扰前端解析结果集。3.2 参数说明与边界限制存储过程暴露了四个参数使用时的建议如下TableName只传表名不要带库名和架构名。如果库里有多个 schema建议把默认 schema 设为 dbo否则 SYS.SYSCOLUMNS 的 OBJECT_ID 解析可能取到错误的表。PageNo从 1 开始内部公式会自动换算成行号区间传 0 或负数会算出无效区间返回空结果。PageSize建议不超过 500因为结果是单列 JSON 文本一次拉太多行会在网络上产生较大的传输开销。OrderByColumn必须传且最好是数字型或唯一键列。如果传了重复值很多的列分页会出现某一行被重复返回或遗漏的情况。执行调用示例-- 第一页每页 20 条按商品编码排序 EXEC proc_GetTableJsonData TableName Merchandise, PageNo 1, PageSize 20, OrderByColumn MerbarCode; -- 第三页每页 50 条 EXEC proc_GetTableJsonData TableName Merchandise, PageNo 3, PageSize 50, OrderByColumn MerID;第一句返回结果大致长这样字段顺序取决于表结构{MerID:101,MerbarCode:6901234567890,MerName:测试商品A,rn:1,total:2048}, {MerID:102,MerbarCode:6901234567891,MerName:测试商品B,rn:2,total:2048},3.3 前端和接口层怎么对接这份 JSON这个脚本是服务端的“数据出口”它本身不提供 Web API但你把 EXEC 的结果集套在一个 HTTP 接口后面前端就能直接消费。常见的对接姿势有两种第一种用 ASP.NET 的 SqlCommand 执行存储过程把返回的第一列逐行读出来拼成一个字符串后通过 WebApi 输出。需要注意对端返回的 JSON 不是完整数组每一行自带逗号你要在代码里统一加方括号包裹。第二种如果你不想写后端可以直接在 SSMS 里执行并把结果粘贴出来给前端联调。这是我最常用的验证方式——先看脚本输出的文本是不是合法 JSON 片段再决定要不要往后端代码里搬。// 前端拿到接口文本后的处理示意 fetch(/api/getJsonData?tableMerchandisepageNo1pageSize20) .then(res res.text()) .then(text { // 后端只做了原样输出这里手动包成数组 const jsonText [ text.replace(/,$/, ) ]; const data JSON.parse(jsonText); console.log(总条数:, data[0]?.total); console.log(当前页行数:, data.length); });3.4 输出样例验证用 SSMS 跑一遍确认结果把存储过程建好后在 SSMS 里直接跑EXEC proc_GetTableJsonData TableName Merchandise, PageNo 1, PageSize 5, OrderByColumn MerID;正常情况下会返回五行单列数据每行是一段 JSON 文本。你可以把结果复制出来前后加上方括号再用在线 JSON 校验工具验证能解析就说明脚本工作正常。这里要特别提醒如果表里字段超过 20 个或者某列的值特别长varchar(3000) 很可能不够用输出会被截断这是这套脚本最典型的翻车点之一下一章单独讲。4. 避坑指南这套手工拼 JSON 脚本的四个踩坑记录4.1 数据里的单引号把 JSON 直接打断现象表里某行数据的备注字段含英文单引号执行后报错“Unclosed quotation mark after the character string”或者不报错但输出里多出一截乱文本。原因脚本只在拼字段片段时用 isnull 做了空值处理没有对字段值里的单引号做转义。动态 SQL 字符串中单引号是字符串边界符值里出现单引号就会提前闭合字符串导致语法错乱或拼接结果被截断。解决在拼接字段值的位置外面再套一层 replace把值里的单引号替换成两个单引号这是 T-SQL 里标准的字符串转义方式。示例写法是isnull(replace(cast(列名 as varchar), , ), )能保证值里的单引号安全进入 JSON 文本。4.2 生成的 JSON 不是合法数组前端 JSON.parse 直接报错现象输出缺头缺尾比如开头少个[或结尾多个逗号前端拿到文本后 JSON.parse 抛异常。原因原设计把“每一行自带逗号”当成数组元素分隔符但没有在结果集开头加左方括号结尾的换行符和多余逗号也没有处理导致文本拼接后不是一个完整 JSON 数组。解决在后端接口做一次收尾处理——拼一个左方括号放在结果集第一行前面右方括号放最后同时去掉最末尾的逗号。上面 fetch 示例里的[ text.replace(/,$/,) ]就是干这个的。4.3 sql 变量扩容问题字段多或数据长导致输出截断现象表有 30 个字段或者某个 varchar 字段存了很长的描述文本执行后 JSON 后半段消失且没有任何报错提示。原因声明写死varchar(3000)拼接的总长度一旦超过 3000 字符就会被静默截断。SQL Server 对普通字符串变量不会主动报长度错误你看到的只是“半截数据”。解决把 sql 声明改成varchar(max)或nvarchar(max)配合存储过程使用基本不会再碰到长度瓶颈。列数特别多的表建议脚本里临时打印一下 sql 的长度观察增长情况。4.4 排序列没有唯一性时出现分页重复或漏行现象同一页数据两次查询结果不一致或某一行跨页出现两次。原因ROW_NUMBER() 的排序依据是 OrderByColumn如果该列存在大量重复值SQL Server 对等值行的排序不保证稳定行号分配可能出现变化。解决排序列尽量选择主键或唯一索引列如果业务必须按非唯一列排序在排序表达式里追加主键作为次级排序比如ORDER BY CategoryID, MerID这样行号分配就是稳定的。5. 把输出修正为直接可用的 JSON 数组定义一个通用封装模板到这里前面的脚本已经能把表数据以 JSON 文本形式取出来但离“前端直接用”还差最后一步——合法数组格式与特殊字符转义。我把自己项目里沉淀的一个通用封装模板分享出来它合并了数组包裹、单引号转义、尾逗号清理三个修复点你复制后改改表名就能用。CREATE PROCEDURE proc_GetTableJsonData_Fixed TableName NVARCHAR(50), PageNo INT, PageSize INT, OrderByColumn NVARCHAR(20) AS BEGIN SET NOCOUNT ON; DECLARE sql AS NVARCHAR(MAX); DECLARE FirstRow INT; DECLARE LastRow INT; SET FirstRow (PageNo - 1) * PageSize 1; SET LastRow PageNo * PageSize; -- 列名循环拼接值里的单引号做双写转义避免动态SQL断裂 SELECT sql ISNULL(sql, with paging as ( select { ) name:isnull(replace(replace(cast(name as varchar),,),char(34),\),), FROM SYS.SYSCOLUMNS WHERE ID OBJECT_ID(TableName); SET sql sql ,}, as sourceText, ROW_NUMBER() OVER(ORDER BY OrderByColumn ) as rn, (SELECT COUNT(*) FROM TableName ) as total FROM TableName ) SELECT [ STUFF(( SELECT sourceText rn: cast(rn as varchar) ,total: cast(total as varchar) }, FROM paging WHERE rn BETWEEN CAST(FirstRow AS VARCHAR) AND CAST(LastRow AS VARCHAR) FOR XML PATH() ), 1, 0, ) ]; EXEC sp_executesql sql; END;这个模板比原版多做三件事第一把 sql 扩容到 NVARCHAR(MAX)长表不再截断第二对字段值做了两次 replace一次处理单引号双写一次把双引号转义成\这样值里即使带引号也不会破坏 JSON 结构第三用 FOR XML PATH 配合 STUFF 把多行文本聚合成一个字符串并在最外层包上左右方括号输出直接就是合法 JSON 数组。至于验证是否成功我的习惯是把存储过程的结果拷出来用 Python 的 json 模块或者在线工具解析一次能通过才交给前端联调。至今我接手过的老项目里凡是手工拼 JSON 的场景问题几乎全出在转义和数组包裹这两处而这套模板把它们前置到 SQL 层解决了。说句实在话这份脚本最打动我的不是拼接技巧本身而是它让我意识到在 FOR JSON 缺席的 SQL Server 版本上T-SQL 的字符串处理能力比大多数人想象的强得多。从那以后我每次写动态 SQL 都会强制走一遍“变量长度、特殊字符、结果格式”三项检查确认输出能被下游直接消费再收工。希望帮到你。本文还有配套的精品资源点击获取 T-SQL手工拼JSON：分页查询脚本详解与避坑指南
简介面向SQL开发人员与数据处理初学者这份技术笔记围绕“用SQL自动生成JSON数据”展开解决将关系型查询结果转换为前端可调用或可持久化JSON的常见问题。文档为docx格式共1个文件压缩包约30KB内容精炼实用。目前已有1141人学习下载被广泛用于日常开发参考。笔记以SQL Server为例完整演示了声明TableName、sql、CurPageFirstRow等变量通过SYS.SYSCOLUMNS系统视图动态获取表结构利用ISNULL与字符串拼接构造查询语句结合ROW_NUMBER()实现分页排序最终由EXEC执行并返回JSON字符串的全过程同时给出将JSON写入数据表的INSERT语句以及前端通过AJAX调用接口获取数据的示例。读者可从中掌握动态SQL构建思路与JSON格式化技巧直接迁移到分页接口或数据交换场景中。1. 用 T-SQL 手工拼 JSON这套分页脚本到底在做什么先说结论在不引入 FOR JSON、不升级到 SQL Server 2016 的前提下纯靠 T-SQL 字符串拼接一样能生成前端能直接用的 JSON 数据。而且这套脚本把“分页查询”和“JSON 序列化”合并成了一次动态 SQL 执行一条语句同时返回当前页数据和总条数接口层连二次查询 count 都省了。我拆这份资源的时候第一反应是写法有点野但思路确实老练——它用 SYS.SYSCOLUMNS 动态取列名用 ROW_NUMBER() 做分页再用 replace 把行记录拼成{字段:值}的格式最后 EXEC 一把梭输出。适合的读者是那些还在用 SQL Server 2008R2/2012、前端又急着要 JSON 接口的老项目维护者新手拿它理解动态 SQL 和分页原理也很划算但直接用之前得先知道它有哪些边界坑。2. 拆解脚本的四个核心部件变量、列名采集、JSON 拼接、分页与执行2.1 变量清单每个变量是干什么的初始化时要注意什么这套脚本最先映入眼帘的是五个 declare 变量它们的分工非常明确TableName目标表名唯一需要你手填的业务参数。sql拼接用的动态 SQL 字符串脚本里特意注释“不要给它赋值”原因是后面要用isnull(sql, ...)做首次初始化如果你提前给了空字符串isnull 就不会走默认分支整个 with paging 的头部就拼不出来了。CurPageFirstRow 和 CurPageLastRow当前页的数据行区间前者是(pageNo-1)*pageSize 1后者是pageNo*pageSize也就是 1 到 20 代表第一页。OrderByColumn排序列名用于 ROW_NUMBER() 的 OVER 子句决定分页的稳定性。这套变量设计最值得借鉴的是把「表名」「排序列」参数化意味着同一个模板可以套到任意表上不用为每个表各写一份分页 SQL。它把分页边界值的计算责任交给了调用方通常是程序里算好了再传进来存储过程本身不做乘法运算这在老项目里是很常见的分工方式。2.2 SYS.SYSCOLUMNS 动态取列为什么用它而不是 INFORMATION_SCHEMA脚本里采集列名的核心语句是SELECT sql ISNULL(sql, with paging as ( select replace({ ) name:isnull(cast(name as varchar),), FROM SYS.SYSCOLUMNS WHERE ID OBJECT_ID(TableName)这段的逻辑是遍历目标表的每一列把列名 name 嵌进 JSON 键的位置同时生成一段取值表达式isnull(cast(列名 as varchar),)两段拼在一起形成列名:列值,这样一小节 JSON 片段。每一列都追加一次最终 sql 里就攒出了完整的行记录模板。这里有两个细节值得说。第一SYS.SYSCOLUMNS 是老兼容视图SQL Server 2000 时代就在用2005 之后官方更推荐 sys.columns但老系统里前者依然可用这套脚本明显是给老库准备的。第二isnull(cast(... as varchar),) 处理了 NULL 值和数值类型转换两个问题——NULL 输出为空字符串数字不会带上科学计数法之类的怪格式。这个处理思路在手工拼 JSON 时是刚需。2.3 replace 拼接 JSON它到底 replace 了什么为什么能拼出行记录脚本里有一行很绕的写法replace({ ... ,)。初学者容易懵我来拆一下。它的目的是每一行数据要拼成{字段1:值1,字段2:值2},这种片段但字段值是动态的所以模板里先写死{然后把“字段名 取值表达式 逗号”的片段循环拼进去最后用一个 replace 把字符串里代表单引号的占位符还原成真实单引号避免字符串嵌套把 T-SQL 搞断。从执行效果上说select 出来的每一行经过这段表达式后就是一个 JSON 对象字符串多个对象的逗号已经在字段片段里带上了后面再统一拼接行尾的 rn 和 total就形成了 JSON 数组的雏形。如果你自己写过类似脚本多半会遇到单引号转义把眼睛看花的问题这套写法用一种比较巧的占位符套路绕过去了。2.4 ROW_NUMBER 分页与 EXEC 执行分页边界怎么算结果怎么返回SELECT 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) EXEC(sql)这一段干了三件事用 ROW_NUMBER() OVER (ORDER BY 排序列) 生成行号 rn同时用标量子查询算出表的总行数 total两个值都会拼进每一行的 JSON 尾巴。外层 where 用 rn between 第一行 and 最后一行 截出当前页数据这是最常见的高效分页写法索引命中情况和 OFFSET 相比在老版本里更可控。EXEC(sql) 动态执行拼好的语句把结果集直接返回给调用方也就是你在 SSMS 里能看到的一列“JSON 文本”。还有一个容易漏掉的点脚本末尾拼接了rn:页码,total:总数},这段尾巴相当于每一行边返回数据边带上自己的行号和总量前端拿到后不用再发一次请求查总数。这在老接口里是很实用的小优化。变量用途初始化注意TableName目标表名必须存在否则 OBJECT_ID 返回 NULL取不到列sql动态 SQL 字符串保持 NULL用 isnull 触发首次初始化CurPageFirstRow当前页起始行号需要调用方预先算好如 (pageNo-1)*pageSize1CurPageLastRow当前页结束行号调用方预先算好如 pageNo*pageSizeOrderByColumn排序列名建议有唯一索引否则分页可能不稳定3. 把这套脚本接到业务里存储过程封装、参数传入、调用与输出确认3.1 封装成可复用的存储过程原脚本是裸的批处理生产环境直接跑没问题但接口层调用不方便。我一般会再加一层壳把它改成存储过程把表名、页码、页大小、排序列都变成入参这样程序里只要一条 EXEC 就能拿到 JSON 文本。下面是完整模板CREATE PROCEDURE proc_GetTableJsonData TableName NVARCHAR(50), PageNo INT, PageSize INT, OrderByColumn NVARCHAR(20) AS BEGIN SET NOCOUNT ON; DECLARE sql AS VARCHAR(3000); DECLARE CurPageFirstRow INT; DECLARE CurPageLastRow INT; SET CurPageFirstRow (PageNo - 1) * PageSize 1; SET CurPageLastRow PageNo * PageSize; -- 先构造 with paging 头部再用列名循环拼字段 JSON 片段 SELECT sql ISNULL(sql, with paging as ( select replace({ ) name:isnull(cast(name as varchar),), FROM SYS.SYSCOLUMNS WHERE ID OBJECT_ID(TableName); -- 补全 paging 查询体行号、总数、数据来源 SET sql sql ,,) as sourceTable, ROW_NUMBER() OVER(ORDER BY OrderByColumn ) as rn, (SELECT COUNT( OrderByColumn ) FROM TableName ) as total FROM TableName ) ; -- 外层拼分页条件和 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); EXEC(sql); END;这里我做了两个改动一是把分页边界值的计算移到过程内部程序调用时只需要传 pageNo 和 pageSize省得每回在代码里手算二是加了 SET NOCOUNT ON避免 DONE_IN_PROC 消息干扰前端解析结果集。3.2 参数说明与边界限制存储过程暴露了四个参数使用时的建议如下TableName只传表名不要带库名和架构名。如果库里有多个 schema建议把默认 schema 设为 dbo否则 SYS.SYSCOLUMNS 的 OBJECT_ID 解析可能取到错误的表。PageNo从 1 开始内部公式会自动换算成行号区间传 0 或负数会算出无效区间返回空结果。PageSize建议不超过 500因为结果是单列 JSON 文本一次拉太多行会在网络上产生较大的传输开销。OrderByColumn必须传且最好是数字型或唯一键列。如果传了重复值很多的列分页会出现某一行被重复返回或遗漏的情况。执行调用示例-- 第一页每页 20 条按商品编码排序 EXEC proc_GetTableJsonData TableName Merchandise, PageNo 1, PageSize 20, OrderByColumn MerbarCode; -- 第三页每页 50 条 EXEC proc_GetTableJsonData TableName Merchandise, PageNo 3, PageSize 50, OrderByColumn MerID;第一句返回结果大致长这样字段顺序取决于表结构{MerID:101,MerbarCode:6901234567890,MerName:测试商品A,rn:1,total:2048}, {MerID:102,MerbarCode:6901234567891,MerName:测试商品B,rn:2,total:2048},3.3 前端和接口层怎么对接这份 JSON这个脚本是服务端的“数据出口”它本身不提供 Web API但你把 EXEC 的结果集套在一个 HTTP 接口后面前端就能直接消费。常见的对接姿势有两种第一种用 ASP.NET 的 SqlCommand 执行存储过程把返回的第一列逐行读出来拼成一个字符串后通过 WebApi 输出。需要注意对端返回的 JSON 不是完整数组每一行自带逗号你要在代码里统一加方括号包裹。第二种如果你不想写后端可以直接在 SSMS 里执行并把结果粘贴出来给前端联调。这是我最常用的验证方式——先看脚本输出的文本是不是合法 JSON 片段再决定要不要往后端代码里搬。// 前端拿到接口文本后的处理示意 fetch(/api/getJsonData?tableMerchandisepageNo1pageSize20) .then(res res.text()) .then(text { // 后端只做了原样输出这里手动包成数组 const jsonText [ text.replace(/,$/, ) ]; const data JSON.parse(jsonText); console.log(总条数:, data[0]?.total); console.log(当前页行数:, data.length); });3.4 输出样例验证用 SSMS 跑一遍确认结果把存储过程建好后在 SSMS 里直接跑EXEC proc_GetTableJsonData TableName Merchandise, PageNo 1, PageSize 5, OrderByColumn MerID;正常情况下会返回五行单列数据每行是一段 JSON 文本。你可以把结果复制出来前后加上方括号再用在线 JSON 校验工具验证能解析就说明脚本工作正常。这里要特别提醒如果表里字段超过 20 个或者某列的值特别长varchar(3000) 很可能不够用输出会被截断这是这套脚本最典型的翻车点之一下一章单独讲。4. 避坑指南这套手工拼 JSON 脚本的四个踩坑记录4.1 数据里的单引号把 JSON 直接打断现象表里某行数据的备注字段含英文单引号执行后报错“Unclosed quotation mark after the character string”或者不报错但输出里多出一截乱文本。原因脚本只在拼字段片段时用 isnull 做了空值处理没有对字段值里的单引号做转义。动态 SQL 字符串中单引号是字符串边界符值里出现单引号就会提前闭合字符串导致语法错乱或拼接结果被截断。解决在拼接字段值的位置外面再套一层 replace把值里的单引号替换成两个单引号这是 T-SQL 里标准的字符串转义方式。示例写法是isnull(replace(cast(列名 as varchar), , ), )能保证值里的单引号安全进入 JSON 文本。4.2 生成的 JSON 不是合法数组前端 JSON.parse 直接报错现象输出缺头缺尾比如开头少个[或结尾多个逗号前端拿到文本后 JSON.parse 抛异常。原因原设计把“每一行自带逗号”当成数组元素分隔符但没有在结果集开头加左方括号结尾的换行符和多余逗号也没有处理导致文本拼接后不是一个完整 JSON 数组。解决在后端接口做一次收尾处理——拼一个左方括号放在结果集第一行前面右方括号放最后同时去掉最末尾的逗号。上面 fetch 示例里的[ text.replace(/,$/,) ]就是干这个的。4.3 sql 变量扩容问题字段多或数据长导致输出截断现象表有 30 个字段或者某个 varchar 字段存了很长的描述文本执行后 JSON 后半段消失且没有任何报错提示。原因声明写死varchar(3000)拼接的总长度一旦超过 3000 字符就会被静默截断。SQL Server 对普通字符串变量不会主动报长度错误你看到的只是“半截数据”。解决把 sql 声明改成varchar(max)或nvarchar(max)配合存储过程使用基本不会再碰到长度瓶颈。列数特别多的表建议脚本里临时打印一下 sql 的长度观察增长情况。4.4 排序列没有唯一性时出现分页重复或漏行现象同一页数据两次查询结果不一致或某一行跨页出现两次。原因ROW_NUMBER() 的排序依据是 OrderByColumn如果该列存在大量重复值SQL Server 对等值行的排序不保证稳定行号分配可能出现变化。解决排序列尽量选择主键或唯一索引列如果业务必须按非唯一列排序在排序表达式里追加主键作为次级排序比如ORDER BY CategoryID, MerID这样行号分配就是稳定的。5. 把输出修正为直接可用的 JSON 数组定义一个通用封装模板到这里前面的脚本已经能把表数据以 JSON 文本形式取出来但离“前端直接用”还差最后一步——合法数组格式与特殊字符转义。我把自己项目里沉淀的一个通用封装模板分享出来它合并了数组包裹、单引号转义、尾逗号清理三个修复点你复制后改改表名就能用。CREATE PROCEDURE proc_GetTableJsonData_Fixed TableName NVARCHAR(50), PageNo INT, PageSize INT, OrderByColumn NVARCHAR(20) AS BEGIN SET NOCOUNT ON; DECLARE sql AS NVARCHAR(MAX); DECLARE FirstRow INT; DECLARE LastRow INT; SET FirstRow (PageNo - 1) * PageSize 1; SET LastRow PageNo * PageSize; -- 列名循环拼接值里的单引号做双写转义避免动态SQL断裂 SELECT sql ISNULL(sql, with paging as ( select { ) name:isnull(replace(replace(cast(name as varchar),,),char(34),\),), FROM SYS.SYSCOLUMNS WHERE ID OBJECT_ID(TableName); SET sql sql ,}, as sourceText, ROW_NUMBER() OVER(ORDER BY OrderByColumn ) as rn, (SELECT COUNT(*) FROM TableName ) as total FROM TableName ) SELECT [ STUFF(( SELECT sourceText rn: cast(rn as varchar) ,total: cast(total as varchar) }, FROM paging WHERE rn BETWEEN CAST(FirstRow AS VARCHAR) AND CAST(LastRow AS VARCHAR) FOR XML PATH() ), 1, 0, ) ]; EXEC sp_executesql sql; END;这个模板比原版多做三件事第一把 sql 扩容到 NVARCHAR(MAX)长表不再截断第二对字段值做了两次 replace一次处理单引号双写一次把双引号转义成\这样值里即使带引号也不会破坏 JSON 结构第三用 FOR XML PATH 配合 STUFF 把多行文本聚合成一个字符串并在最外层包上左右方括号输出直接就是合法 JSON 数组。至于验证是否成功我的习惯是把存储过程的结果拷出来用 Python 的 json 模块或者在线工具解析一次能通过才交给前端联调。至今我接手过的老项目里凡是手工拼 JSON 的场景问题几乎全出在转义和数组包裹这两处而这套模板把它们前置到 SQL 层解决了。说句实在话这份脚本最打动我的不是拼接技巧本身而是它让我意识到在 FOR JSON 缺席的 SQL Server 版本上T-SQL 的字符串处理能力比大多数人想象的强得多。从那以后我每次写动态 SQL 都会强制走一遍“变量长度、特殊字符、结果格式”三项检查确认输出能被下游直接消费再收工。希望帮到你。本文还有配套的精品资源点击获取