SQL Server动态SQL生成JSON分页接口与存储过程封装

简介：一份聚焦 SQL 自动生成 JSON 数据的 docx 技术文档，面向数据库开发、后端接口开发及需要将关系表数据快速转为 JSON 的初中级技术人员，重点解决手工拼接 JSON 字符串繁琐、分页查询与字段动态适配困难等问题。压缩包共 1 个 docx 文件，约 30KB，篇幅集中，便于随查随用。文档围绕 SQL Server 环境展开，涉及变量声明、动态 SQL 拼接、SYS.SYSCOLUMNS 读取表结构、WITH 分页与 ROW_NUMBER() 排序编号，并给出将结果组装为 JSON、通过 EXEC 执行及写入数据表的示例；同时说明前端借助 AJAX 调用接口获取 JSON 数据的思路。已有 1148 人学习，适合需要理解动态字段转 JSON、分页输出与前后端数据对接的读者参考，也可作为项目中的拼接模板与排错线索。

1. 一条 SQL 直接吐 JSON 的老派做法，救了多少没写完的接口

前阵子帮某开发者看他手上一个仓储系统的列表页，后端请了假，前端对着空表格干等。他把一段存到文档里的脚本转给我，说这东西能把整张表直接变成带分页信息的 JSON 数组——我跑了一遍，确实能。这个「SQL 自动生成 JSON 数据」的思路，核心就三件事：从sys.columns读出字段名动态拼出键，用ROW_NUMBER()切页，最后EXEC把拼好的语句扔回去执行。它适合谁？手上跑着 SQL Server 2005 到 2014 这类没有FOR JSON的老库，又不想为了一个列表接口专门写一层后端序列化代码的场景。往下读，你能把这段脚本完整跑通，也能知道它在哪些地方会翻车、怎么改才敢上线。

2. 动态拼接骨架：sys.columns怎么把一行数据拼成 JSON 键值对

这套脚本真正的难点不在分页，而在“怎么在不写死字段名的前提下，把一张表的所有列拼成"key":"value"这种形状”。作者选了字典表SYS.SYSCOLUMNS（新版对应sys.columns），在编译期拿到列名，把列名当作字面量拼进一段待执行的 SQL 文本里。理解这一步，后面的分页只是加两条尾巴。

2.1 五个变量定死整个脚本的输入面

脚本开头只声明了五个变量，看起来简陋，但这五个恰好覆盖了“查哪张表、怎么排、取哪一段、装到哪”四个问题。类型选得比较保守，varchar(3000)和varchar(20)这种长度在真实业务表上很容易撑爆，后面第 4 章会专门讲这个坑。

变量	类型	作用	常见取值
@TableName	varchar(50)	目标表名，直接拼进动态 SQL	'Merchandise'
@sql	varchar(3000)	承载最终拼出来的整条语句	初值必须是NULL
@CurPageFirstRow	int	本页第一行在结果集中的序号	(pageNo-1)*pageSize+1
@CurPageLastRow	int	本页最后一行序号	pageNo*pageSize
@OrderByColumn	varchar(20)	ROW_NUMBER()的排序依据列	'MerbarCode'

这里有个很容易被忽略的细节：@sql的初值必须是NULL而不是空串。因为后面的拼接用的是ISNULL(@sql, 'with paging as ( ...')，只有NULL才会触发这个兜底前缀。有人图省事写了SET @sql = ''，结果整条语句从with中间开始，EXEC直接抛语法错误，排查起来还挺费时间。

DECLARE @TableName varchar(50) = 'Merchandise'; -- 要查的表 DECLARE @OrderByColumn varchar(20) = 'MerbarCode'; -- 排序依据 DECLARE @CurPageFirstRow int = 1; -- (pageNo-1)*pageSize+1 DECLARE @CurPageLastRow int = 20; -- pageNo*pageSize DECLARE @sql varchar(3000); -- 注意：不赋值，保持 NULL

赋值语句本身没什么可说的，但把@CurPageFirstRow和@CurPageLastRow交给调用方算，是个有意的设计取舍：脚本不关心页码，只认行号区间。好处是存储过程或前端算错了也不会在这里二次出错；坏处是pageNo传 0 或负数时没有任何校验，BETWEEN -19 AND 0会静默返回空结果集，前端就只能看到一个空白列表。

2.2 一行 SELECT 把整张表的字段片段拼出来

核心拼接只有一条SELECT语句，但它用了@sql = @sql + ...这种“累加赋值”的写法，把sys.columns里的每一行变成一段 JSON 片段文本。这是 SQL Server 里做字符串聚合的老手法，在STRING_AGG出现之前基本是唯一选择。

SELECT @sql = ISNULL(@sql, N'with paging as ( select replace(''{''+ ') + N'''"' + c.name + N'":"'' + ISNULL(CAST(' + c.name + N' AS varchar), '''') + N''',''' FROM sys.columns c WHERE c.object_id = OBJECT_ID(@TableName) ORDER BY c.column_id; -- 原脚本没写排序，字段顺序不稳定

拆开看这段在拼什么：第一次进入循环时@sql是NULL，ISNULL取第二个参数，也就是with paging as ( select replace('{'+这段前缀。之后的每一次循环都在后面追加一段'"列名":"'+ISNULL(CAST(列名 AS varchar),'')+'",'——注意这里拼的是待执行 SQL 的文本，不是结果。列名是编译期就已知的，所以直接当字面量写进去；而“值”那一部分被拼成了运行时才会求值的表达式。

OBJECT_ID(@TableName)负责把表名翻译成对象 ID，sys.columns里一行对应一个字段。如果表名写错或者当前登录账号没权限，这里返回空集，@sql会停留在NULL，后面的EXEC直接报“参数未定义”，报错信息和真正原因隔了十万八千里——这类空集导致的连锁报错在动态 SQL 里特别常见。

我在原脚本基础上补了ORDER BY c.column_id。别小看这一句，不写的话字段顺序由执行计划决定，同一张表两次调用可能给出不同的键顺序，虽然 JSON 语义上无序，但前端如果是拿Object.keys()的位置做解析，就会变成偶发问题。

2.3 用@q变量把引号层数降下来

上面那段代码最劝退的地方是引号。原作者为了让拼出来的文本里出现"，用了''''这样的多层转义，加上replace的查找串和替换串，一行里能出现七八个连续单引号。改一个字符就可能全盘失效，而且报错信息完全指不到出问题的那一行。

我的习惯是把双引号先存进变量，拼接时只拼变量名：

DECLARE @q char(1) = '"'; -- 双引号，避免每处都写 '''' DECLARE @cols nvarchar(max) = N''; SELECT @cols = @cols + CASE WHEN ROW_NUMBER() OVER (ORDER BY c.column_id) = 1 THEN N'' ELSE N',' END -- 首列不加分隔符，天然没有尾逗号 + @q + c.name + @q + N':' + N'''' + N'ISNULL(CONVERT(nvarchar(max), ' + QUOTENAME(c.name) + N'), N'''')' + N'''' + @q FROM sys.columns c WHERE c.object_id = OBJECT_ID(@TableName);

这段拼出来的@cols大概长这样（以一张三列的表为例）：

"{'" + '"MerbarCode":"' + ISNULL(CONVERT(nvarchar(max), [MerbarCode]), N'') + '"' + ',"MerName":"' + ISNULL(CONVERT(nvarchar(max), [MerName]), N'') + '"' + ',"MerQty":"' + ISNULL(CONVERT(nvarchar(max), [MerQty]), N'') + '"' + '"}' -- 实际拼装时首尾由外层补齐

跟原脚本比，改动有三个：一是用CASE判断首列不加逗号，从根上避免了“拼完再 replace 掉尾逗号”这种事后补救；二是CONVERT(nvarchar(max), ...)取代CAST(... AS varchar)，中文不会掉字；三是QUOTENAME(c.name)只用在值表达式里，JSON 的键仍然是原始列名，不会被加上方括号。

提示：QUOTENAME默认加的是[]，如果你的列名本身带空格或特殊字符，键那边也得单独处理，不能直接复用QUOTENAME的结果。

到这里，@sql里已经有了一段完整的字段拼装表达式，剩下的工作就是把它套进WITH paging AS (...)里，并补上分页和总数。这套“先把结构拼成文本、再交给引擎执行”的模式，本质上就是手写一个极简的 JSON 序列化器，只不过它跑在数据库进程里。

3. 分页与总数：ROW_NUMBER()和count()一次拼出三样数据

字段片段搞定之后，分页反而是最规整的部分。整套逻辑围绕一个 CTE 展开：CTE 内部负责给每一行编号、算总数、把列值拼成 JSON 片段；CTE 外部负责按行号区间筛选。这个结构在 SQL Server 2005 之后就通用了，不依赖任何新版本特性，也是它在老系统里能活下来的原因。

3.1 CTE +ROW_NUMBER()构出分页骨架

先看把第 2 章产物套进去之后，@sql拼出来的完整语句长什么样：

with paging as ( select '{"MerbarCode":"' + ISNULL(CONVERT(nvarchar(max), [MerbarCode]), N'') + '"' + ',"MerName":"' + ISNULL(CONVERT(nvarchar(max), [MerName]), N'') + '"' + ',"MerQty":"' + ISNULL(CONVERT(nvarchar(max), [MerQty]), N'') + '"' + '}' as sourceTable, ROW_NUMBER() OVER (ORDER BY MerbarCode) as rn, (select count(*) from Merchandise) as total from Merchandise ) select * from paging where rn between 1 and 20;

ROW_NUMBER() OVER (ORDER BY MerbarCode)是整段分页的心脏。它给结果集按指定列排序后从 1 开始编号，编号在本次查询内唯一且连续。外层WHERE rn BETWEEN 1 AND 20直接切出第一页。这种写法和OFFSET ... FETCH（SQL Server 2012+）功能等价，但兼容性更好，在 2005/2008 上也能跑。

ORDER BY后面的列名同样是拼进去的字面量，不是变量。这就意味着@OrderByColumn的值必须是真实存在的列名，写错了会在EXEC阶段才报“列名无效”，静态检查完全发现不了。

排序稳定性是这类分页最常见的隐患。如果MerbarCode上有大量重复值，ROW_NUMBER()给出的序号在两次执行之间可能不同（取决于执行计划），翻页时就会出现某条记录在第 1 页和第 2 页都出现、或者干脆被跳过。稳妥做法是在排序里补一个唯一列兜底，比如ORDER BY MerbarCode, MerId。

3.2total子查询：把总数塞进每一行

分页列表通常还要返回总数，否则前端画不出页码。原脚本的做法是在 CTE 里放一个标量子查询(select count(...) from 表)，SQL Server 会把它当常量对待，整张表只算一次，不会每行都跑。

-- 原脚本写法：count(排序列) (select count(MerbarCode) from Merchandise) as total -- 更稳的写法：count(*)，不受 NULL 影响 (select count_big(*) from Merchandise) as total

这两个版本的差别在 NULL 上。COUNT(列名)只统计非 NULL 的行，如果排序列恰好允许为空，总数就会比实际行数少，前端算出来的总页数跟着偏小，最后一页永远翻不到。改成COUNT(*)或COUNT_BIG(*)就没这个问题；表特别大的话用COUNT_BIG，避免 int 溢出。

标量子查询在这里是能用的，但它有一个前提：CTE 内部没有WHERE过滤条件。一旦你在 CTE 里加了业务过滤（比如只查未删除的记录），这个count也必须同步加上同样的条件，否则总数和列表对不上。这是二次开发时最容易漏改的地方。

3.3 外层收尾：把sourceTable、rn、total拼成一个完整对象

第一版脚本到select * from paging where rn between ...就结束了，返回的每一行里sourceTable是一个 JSON 对象字符串，rn和total是独立的两列。前端拿到手还得自己再拼一次，体验不好。所以原文档里还给了第二版：

select sourceTable + '"rn":"' + CAST(rn as varchar) + '",' + '"total":"' + CAST(total as varchar) + '"},' from paging where rn between 1 and 20;

这样每一行输出的就是一个完整的 JSON 对象，末尾带一个逗号，方便调用方按行直接concat成数组。rn和total被塞进对象内部，前端解析一次就能同时拿到数据和分页信息。

但这一版有两个明显的半成品痕迹。一是没有在最外层包[和]，输出的是若干个用逗号分隔的对象，不是合法 JSON 数组；二是每个对象（包括最后一个）后面都跟着逗号，直接拼起来会多一个尾逗号，JSON.parse会抛错。作者大概是打算让前端自己处理，但这属于把复杂度推给了调用方。

要收干净，两种方式。老版本用FOR XML PATH('')把行拼成一个字符串，再去掉尾逗号：

DECLARE @json nvarchar(max); SELECT @json = N'[' + STUFF(( SELECT N',' + sourceTable + N'"rn":"' + CAST(rn as varchar) + N'","total":"' + CAST(total as varchar) + N'"}' FROM paging WHERE rn BETWEEN @CurPageFirstRow AND @CurPageLastRow FOR XML PATH(''), TYPE ).value('.', 'nvarchar(max)'), 1, 1, N'') + N']';

STUFF(..., 1, 1, '')的作用是删掉拼接结果开头的第一个逗号。FOR XML PATH('')负责把多行拼成一个字符串，.value('.', 'nvarchar(max)')把 XML 类型转回文本，顺带处理了实体转义。这套组合在 2005 上就能用，是STRING_AGG之前的通用解法。SQL Server 2017 之后可以直接换成STRING_AGG(sourceTable, ',')，代码短一半。

注意：FOR XML PATH会转义&、<、>这些字符，转成&之类。如果字段值里本来就有这些符号，前端拿到的是被二次转义的文本，还得再解一次。用STRING_AGG就没这个副作用。

3.4EXEC(@sql)之后，调用方到底拿到什么

最后一句是EXEC(@sql)。它把拼好的整条语句当字符串执行，结果集直接返回给客户端。客户端看到的是一张表，每行一列，列名是sourceTable（第二版里没有别名，列名会是空或者表达式文本），值是一个 JSON 对象字符串。

这里有个行为差异值得记住：EXEC(@sql)只能把结果集吐给调用方，没法直接SET @变量 = EXEC(...)。想在存储过程内部把结果留下来继续加工，必须借道临时表：

CREATE TABLE #j (line nvarchar(max)); INSERT INTO #j (line) EXEC(@sql); -- 先落表，再加工

INSERT ... EXEC有几个限制：不能嵌套（被执行的语句里不能再有INSERT ... EXEC），不能和OUTPUT子句混用，而且目标表必须先建好、列数和类型要匹配。这些限制在写一次性脚本时无所谓，一旦要封装成通用存储过程就会撞上，第 5 章会换一条路走。

4. 避坑与排查：这条脚本最容易翻车的五个地方

这段脚本短，但每一行都埋着雷。下面五条是我和周边人实际踩过的，按“现象 → 原因 → 解决”记下来，照着自己那份脚本对一遍，能省不少排查时间。

第一坑：字段一多就报语法错误

现象：表只有七八列时跑得好好的，业务加了几个字段之后突然开始报Incorrect syntax near ...或者Unclosed quotation mark after the character string，回滚几个字段又正常。

原因：@sql声明成了varchar(3000)。动态 SQL 的文本长度随列数线性增长，每列大约贡献 60～80 个字符，四十列左右就顶到上限。varchar在赋值时会静默截断，不报错，截断后的语句多半停在某个字符串中间，EXEC阶段才炸。

解决：@sql直接声明成nvarchar(max)，@TableName和@OrderByColumn用sysname（等价nvarchar(128)）。顺带把拼接过程中的所有字面量都加上N前缀，不然中文字符串会被降级成varchar，前面的nvarchar(max)白改了。

第二坑：字段值里的逗号被吃掉了

现象：某个商品名原本是螺丝, M6，接口返回变成螺丝 M6，逗号凭空消失。数据表里查出来是好的，只有接口返回的不对。

原因：原作者用replace(拼接结果, ',', '')去清理末尾多余的逗号。这个replace作用在整行拼接结果上，不区分“结构分隔符”和“内容里的逗号”，值里出现的逗号一样被删。

解决：别用replace事后清理，改成拼接时首列不加分隔符的写法（见 2.3），从根上没有尾逗号。如果已经在线上跑着不好大改，至少把查找串改得更精确，比如先拼成{...,"x":"1",}这种固定形态，再用LEFT(s, LEN(s)-1)砍掉最后一个字符，而不是全局替换。

第三坑：中文字段变成问号

现象：nvarchar列里的中文，输出到 JSON 里变成?或者空白。

原因：CAST(列名 AS varchar)会按当前数据库的排序规则做代码页转换，非中文代码页下汉字直接丢失，且不报错。

解决：CONVERT(nvarchar(max), 列名)，同时拼接用的字面量全部加N前缀，@sql用nvarchar(max)。三处缺一不可，只改一处还是会丢。

第四坑：值里有双引号或换行，前端JSON.parse直接抛错

现象：大部分数据正常，偶尔某个接口 500，日志里看不出所以然。把返回体打印出来，发现某个值里带了个双引号。

原因：脚本只做了 NULL 处理，没做 JSON 转义。"、\、换行、制表符这些字符在 JSON 里都有特殊含义，原样输出就是非法 JSON。

解决：在拼值的那一层套REPLACE：

-- 顺序很重要：反斜杠必须先处理，否则会把后面加的转义符再转一遍 REPLACE(REPLACE(REPLACE(REPLACE(v, N'\', N'\\'), N'"', N'\"'), CHAR(13), N'\r'), CHAR(10), N'\n')

SQL Server 2016 之后有STRING_ESCAPE(v, 'json')，一行搞定，但注意它不处理控制字符，换行还得单独REPLACE。

第五坑：翻页出现重复行，总数对不上

现象：第 1 页的最后一条在第 2 页第一条又出现一次；或者前端算出来 5 页，翻到第 5 页是空的。

原因有两个，经常同时出现。一是ORDER BY的列有重复值，ROW_NUMBER()在并列行之间的编号不稳定，不同次执行给出不同顺序；二是总数用了COUNT(排序列)，排序列上有 NULL 时少算行。

解决：排序补一个唯一列做次级排序（ORDER BY MerbarCode, MerId）；总数改COUNT(*)或COUNT_BIG(*)。如果业务上确实需要按可能重复的列排序，就把主键永远挂在排序列表最后。

5. 工程化封装：从一段粘贴脚本到可调用的存储过程

脚本放在文档里，每次用的时候改改变量再跑，在临时查数时没问题。但要做成接口，就得把它封装成一个有明确入参、出参、错误处理的存储过程。这一章把散在前面的碎片拼起来，给出一个能直接建到库里的版本。

5.1 存储过程签名与参数校验

签名设计上，我把“页码 + 每页条数”作为对外参数，行号区间在内部算，这样调用方不用关心BETWEEN的边界。出参用OUTPUT直接把 JSON 字符串带出去，调用方拿到就是一段可以直接塞进 HTTP 响应的文本。

CREATE OR ALTER PROCEDURE dbo.usp_TableToJsonPage @TableName sysname, @OrderByColumn sysname, @PageNo int = 1, @PageSize int = 20, @Json nvarchar(max) OUTPUT AS BEGIN SET NOCOUNT ON; -- 入参兜底，避免前端传 0 或负数导致静默返回空集 IF @PageNo < 1 SET @PageNo = 1; IF @PageSize < 1 SET @PageSize = 20; IF @PageSize > 500 SET @PageSize = 500; -- 上限保护，防止一次拉全表 -- 表名必须真实存在，否则 sys.columns 返回空集，后续拼接全是 NULL IF OBJECT_ID(@TableName, N'U') IS NULL BEGIN RAISERROR(N'表不存在或不是用户表: %s', 16, 1, @TableName); RETURN; END; DECLARE @FirstRow int = (@PageNo - 1) * @PageSize + 1; DECLARE @LastRow int = @PageNo * @PageSize; ... END

OBJECT_ID(@TableName, N'U')里的'U'限定只匹配用户表，避免误传视图名或系统表。RAISERROR的严重级别用 16，属于用户可修复的错误，客户端能正常捕获。@PageSize加 500 的上限，是因为PageSize传大了等于全表扫描加全量字符串拼装，一次就能把内存打满。

5.2 拼字段时的转义与类型处理

在 2.3 的版本基础上，把转义补进值表达式。注意转义要作用在值上，不能作用在整行结果上，否则又回到第二坑。

DECLARE @cols nvarchar(max) = N''; SELECT @cols = @cols + CASE WHEN ROW_NUMBER() OVER (ORDER BY c.column_id) = 1 THEN N'' ELSE N',' END + N'"' + c.name + N'":"' + N''' + ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(nvarchar(max), ' + QUOTENAME(c.name) + N'), N''\'', N''\\''), N''"'', N''\"''), CHAR(13), N''\r''), CHAR(10), N''\n''), N'''') + N'"' FROM sys.columns c WHERE c.object_id = OBJECT_ID(@TableName);

这段生成的是待执行 SQL 里的表达式片段，四层REPLACE的转义在拼接时需要写成双写引号，可读性很差但逻辑清楚：内层先把列值转成nvarchar(max)，再依次替换反斜杠、双引号、回车、换行，最后ISNULL兜底成空串。反斜杠必须排在最前面，这个顺序写反了会把后面生成的\"再转义一次，变成\\"，JSON 依旧非法。

日期和数值类型这里偷了个懒，直接CONVERT(nvarchar(max), ...)，日期会变成Jan 1 2024 12:00AM这种默认格式。如果前端要标准 ISO 格式，把日期列单独挑出来做CONVERT(varchar(19), 列, 120)，做法是按sys.types.name判断：

CASE WHEN t.name IN ('datetime', 'datetime2', 'smalldatetime') THEN N'CONVERT(varchar(19), ' + QUOTENAME(c.name) + N', 120)' ELSE N'CONVERT(nvarchar(max), ' + QUOTENAME(c.name) + N')' END

120是 ODBC 标准格式yyyy-mm-dd hh:mi:ss，前端new Date()能直接解析。这段要加JOIN sys.types t ON c.user_type_id = t.user_type_id才能拿到类型名。

5.3 用sp_executesql执行并回收结果

前面提过EXEC(@sql)没法直接给变量赋值。这里换成sp_executesql，它能声明 OUTPUT 参数，让被执行的语句把结果写回外层变量，比INSERT ... EXEC干净，也不受“不能嵌套”的限制。

DECLARE @stmt nvarchar(max) = N' WITH paging AS ( SELECT ' + @cols + N' AS sourceTable, ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@OrderByColumn) + N') AS rn, COUNT_BIG(*) OVER () AS total FROM ' + QUOTENAME(@TableName) + N' ) SELECT @out = N''['' + STUFF(( SELECT N'','' + sourceTable + N''"rn":"'' + CAST(rn AS varchar) + N''","total":"'' + CAST(total AS varchar) + N''"}'' FROM paging WHERE rn BETWEEN @a AND @b FOR XML PATH(''''), TYPE ).value(''.'', ''nvarchar(max)''), 1, 1, N'''') + N'']'';'; EXEC sp_executesql @stmt, N'@a int, @b int, @out nvarchar(max) OUTPUT', @a = @FirstRow, @b = @LastRow, @out = @Json OUTPUT;

COUNT_BIG(*) OVER ()是窗口聚合，一次扫描就能把总数带到每一行，比 3.2 里的标量子查询少扫一遍表。QUOTENAME(@TableName)给表名加方括号，顺带挡住表名里带空格的情况——但它挡不住注入，QUOTENAME遇到超长输入会返回NULL，这点在第 6 章会补白名单。

外层@stmt里用@a、@b、@out三个参数占位，sp_executesql负责把值传进去。表名和排序列没法参数化，只能拼，这是动态 SQL 的固有局限，只能靠白名单约束。执行计划方面，sp_executesql比EXEC(@sql)好一点，参数化部分能复用计划，尽管表名一变计划还是会重新生成。

接口层拿到@Json之后基本就是透传：

// 某后端服务的列表接口，拿到存储过程返回值直接透出 const pool = await sql.connect(config); const result = await pool.request() .input('TableName', sql.NVarChar(128), 'Merchandise') .input('OrderByColumn', sql.NVarChar(128), 'MerbarCode') .input('PageNo', sql.Int, req.query.page || 1) .input('PageSize', sql.Int, req.query.size || 20) .output('Json', sql.NVarChar(sql.MAX)) .execute('dbo.usp_TableToJsonPage'); res.type('application/json').send(result.output.Json);

sql.NVarChar(sql.MAX)这个类型声明必须和存储过程的OUTPUT参数严格对齐，写成sql.NVarChar不带长度会默认 4000，超过就截断。这类“类型对不上导致数据截断”的问题不报错，只表现为前端偶尔少几条数据，很难往驱动层想。

6. 二次开发：白名单、换分页位置、以及怎么验证改对了

这套脚本真正要投入二次开发，我一般从三个地方下手，按改动风险从低到高排。

第一件事是给表名和排序列加白名单。QUOTENAME(@TableName)挡不住Merchandise; DROP TABLE ...这种输入，因为分号在QUOTENAME包裹之后仍然是语句分隔符。最省事的做法是建一张配置表，把允许暴露的表和排序列登记进去，进存储过程第一件事就是EXISTS校验：

IF NOT EXISTS (SELECT 1 FROM dbo.JsonApiWhitelist WHERE TableName = @TableName AND OrderColumn = @OrderByColumn) BEGIN RAISERROR(N'表或排序列未授权: %s / %s', 16, 1, @TableName, @OrderByColumn); RETURN; END;

维护成本不高，新增一张表加一行记录就行，比在代码里硬编码IF @TableName = 'A' OR @TableName = 'B'好扩展。我一般还会在这张表上加一列MaxPageSize，不同表给不同的上限，大表限制严一点。

第二件事是把 JSON 拼装挪到分页之后。原脚本的结构是“先对全表拼 JSON，再按rn切页”。SQL Server 通常会做谓词下推，但sourceTable是 CTE 内的计算列，优化器不一定愿意把它推迟到外层，表大的时候会白算几万行字符串。改成先在 CTE 里只算rn和total，外层SELECT再拼：

WITH paged AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY MerbarCode, MerId) AS rn, COUNT_BIG(*) OVER () AS total FROM dbo.Merchandise ) SELECT '{"MerbarCode":"' + ... + '"}' FROM paged WHERE rn BETWEEN @a AND @b;

这样字符串拼装只对本页的 20 行做，代价是 CTE 里SELECT *会把宽列全带出来排序。如果表特别宽，可以只SELECT需要的列，效果更明显。我在一张 30 万行、40 列的表上对比过，改前单次约 900ms，改后 120ms 左右，差距主要来自字符串拼装的次数。

第三件事，如果数据库版本够，直接换FOR JSON。SQL Server 2016 起原生支持：

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY MerbarCode, MerId) AS rn FROM dbo.Merchandise ) t WHERE rn BETWEEN 1 AND 20 FOR JSON PATH, INCLUDE_NULL_VALUES;

转义、类型、空值全由引擎处理，不用自己拼字符串，也不用担心注入。缺点一是版本门槛，二是输出的键名跟列名完全一致，没法做别名映射（除非在子查询里AS）。老系统升不上去，就只能继续用前面那套。

验证方法。我一般准备一张回归表，塞四类脏数据：值里带逗号、值里带双引号和反斜杠、整行全 NULL、中文加 emoji。改完脚本跑一遍，SQL Server 2016+ 直接用ISJSON(@Json)判断是否合法，返回 1 才算过；老版本就把@Json贴到浏览器控制台跑JSON.parse。再

本文还有配套的精品资源，点击获取