从表返回JSON到变量的SP仅返回前8000字符，如何支持更长字符串？

问题 #

我尝试创建一个存储过程（SP），用于从指定表返回JSON数据，并将其存储到变量中以进行格式化和拼接额外信息。当数据集较小时，该存储过程运行正常，但数据集较大时，仅返回前8000个字符。请问是否有办法让该存储过程返回更长的字符串？

我使用Azure Studio直接执行SELECT语句时可以返回超过8000字符的字符串，但无法将其存储到变量中。

存储过程代码：

CREATE OR ALTER PROCEDURE [dbo].[spGetJsonFromTable]
(
    @tableName VARCHAR(128),
    @jsonString VARCHAR(MAX) OUTPUT
)
AS
BEGIN
    DECLARE @SQL NVARCHAR(4000);

    SET @SQL = N'SET @jsonString = (SELECT * FROM ' + @tableName + N' FOR JSON AUTO, INCLUDE_NULL_VALUES);';
    EXEC [sys].[sp_executesql] @SQL,
                               N'@jsonString VARCHAR(MAX) OUT',
                               @jsonString OUT;
END;
GO

存储过程调用代码：

DECLARE @jsonString VARCHAR(MAX);
EXEC [dbo].[spGetJsonFromTable] @tableName = 'mytable',       -- varchar(128)
                                @jsonString = @jsonString OUTPUT; -- varchar(max)

解决方案 #

问题根源在于动态SQL中FOR JSON结果的隐式类型转换：即使你声明了@jsonString为VARCHAR(MAX)，但动态SQL内部会默认将FOR JSON的输出当作VARCHAR(8000)处理，导致超过长度的内容被截断。以下两种方法可以解决这个问题：

方法1：改用NVARCHAR(MAX)类型 #

将存储过程的输出参数、动态SQL中的参数声明全部替换为NVARCHAR(MAX)，SQL Server对Unicode字符串的长内容处理更稳定，不会触发截断：

修改后的存储过程：

CREATE OR ALTER PROCEDURE [dbo].[spGetJsonFromTable]
(
    @tableName VARCHAR(128),
    @jsonString NVARCHAR(MAX) OUTPUT
)
AS
BEGIN
    DECLARE @SQL NVARCHAR(4000);

    SET @SQL = N'SET @jsonString = (SELECT * FROM ' + @tableName + N' FOR JSON AUTO, INCLUDE_NULL_VALUES);';
    EXEC [sys].[sp_executesql] @SQL,
                               N'@jsonString NVARCHAR(MAX) OUT',
                               @jsonString OUT;
END;
GO

对应的调用代码也要同步修改变量类型：

DECLARE @jsonString NVARCHAR(MAX);
EXEC [dbo].[spGetJsonFromTable] @tableName = 'mytable',
                                @jsonString = @jsonString OUTPUT;

方法2：显式转换为VARCHAR(MAX) #

如果必须使用VARCHAR(MAX)，可以在动态SQL中对FOR JSON的结果做显式类型转换，强制保留完整长度：

修改后的存储过程：

CREATE OR ALTER PROCEDURE [dbo].[spGetJsonFromTable]
(
    @tableName VARCHAR(128),
    @jsonString VARCHAR(MAX) OUTPUT
)
AS
BEGIN
    DECLARE @SQL NVARCHAR(4000);

    SET @SQL = N'SET @jsonString = CAST((SELECT * FROM ' + @tableName + N' FOR JSON AUTO, INCLUDE_NULL_VALUES) AS VARCHAR(MAX));';
    EXEC [sys].[sp_executesql] @SQL,
                               N'@jsonString VARCHAR(MAX) OUT',
                               @jsonString OUT;
END;
GO

两种方法都能确保返回完整的长JSON字符串，不会出现截断问题。

内容的提问来源于stack exchange，提问作者DennisT