SQL存储过程用JSON AUTO无数据时返回null，需返回含空记录的JSON

问题说明 #

存储过程通过固定列+动态获取的Question列生成结果，无匹配记录时，直接用FOR JSON AUTO会返回null，需要返回包含所有列名、值为null的单条记录JSON数组。

原存储过程代码 #

ALTER PROCEDURE [dbo].[GetVendorCriticalityInventory]
    @CategoryName VARCHAR(120) = null 
AS
BEGIN
    DECLARE @sql1 Nvarchar(Max);
    DECLARE @ques Nvarchar(max);

    DECLARE @CommonColumns NVARCHAR(MAX) = '
RID,
RequestID,
[Application/Vendor Name],
[Risk Identified],
[Application/Vendor Type],
[Date of the Request],
[IT Owner],
[Business Owner],
Requestor,
[App/Vendor Criticality],
ISOClause,
Submodule
';

    SELECT @ques = STRING_AGG(CAST(QUOTENAME(Question) AS varchar(MAX)),',') 
    FROM
        (select distinct Question 
         from TPMSMasterData 
         where TPMSCategoryName = @CategoryName) AS D

    SET @sql1 = '

    WITH LatestData AS 
    (
        select
            R.RequestID as RID,
            R.RequestIDInfo as RequestID,
            isnull(R.NameOftheTool, ''N/A'') as [Application/Vendor Name],
            RiskSummaryScore as [Risk Identified],
            isnull(convert(varchar(10), R.RequestSubmitted, 120), ''N/A'') as [Date of the Request],
            isnull(R.NameOfModule, ''N/A'') as [Application/Vendor Type],
            ''N/A'' as [IT Owner],
            isnull(R.BusinessOwners, ''N/A'') as [Business Owner],
            isnull(R.RequestCreatedByEmailID, ''N/A'') as Requestor,
            isnull(cast(R.AppCriticalityScore AS varchar), ''N/A'') AS [App/Vendor Criticality],
            isnull(A.Question, ''N/A'') as Question,
            R.ISOClause,
            R.Submodule,
            isnull(case
                       when exists (SELECT 1 
                                    FROM STRING_SPLIT(A.DataControlLup, '':'') AS sg 
                                    WHERE TRIM(sg.value) = ''LookupTable'' ) 
                           then d.lookupDisplayValue 
                       when A.DataControlLup like ''%[A-Za-z]%'' 
                           then c.LupDisplayValue 
                       else b.Data1 
                   end, 
            ''N/A'') as Data1 ,
            row_number() over (partition by B.RequestID, A.Question order by B.TPMSTransID desc) as rn
        from
            TPMSMasterData A
        join
            TPMSTransactionData B on A.TPMSID = b.TPMSID
        join
            RequestInfo R on B.RequestID = R.RequestID
        left join
            BusinessLookupTable c on TRY_CAST(b.Data1 as int) = c.BusinessLupID
        left join 
            LookupTable d on try_cast(B.Data1 AS INT) = d.LookupID
        where
            A.TPMSCategoryName = @CategoryName 
            and R.WorkFlowCompleted = 1 

) 
SELECT(
    select ' + @CommonColumns + ', ' + @ques + '
    from (
        SELECT ' + @CommonColumns + ',Question,Data1
        FROM LatestData 
        WHERE rn = 1
    ) as TPMSCategory
    pivot(max(Data1) for Question in (' + @ques + ') ) as LatestAnswer
    for json auto
) AS [my_json]
';

exec sp_executesql @sql1,N'@CategoryName VARCHAR(120)',@CategoryName
END
GO

当前无记录时输出 #

期望输出 #

尝试的修改及错误 #

尝试生成全null列并与原结果合并，但因重复列名报错：

SELECT @DummyColumns = STRING_AGG('NULL AS ' + TRIM(value), ', ')
 FROM STRING_SPLIT(@CommonColumns + ',' + @ques, ',');  
,
 TPMSCategory AS (
     SELECT ' + @CommonColumns + ', Question, Data1
     FROM LatestData
     WHERE rn = 1
 ),
 LatestAnswer AS (
     SELECT ' + @CommonColumns + ', ' + @ques + '
     FROM TPMSCategory
     PIVOT (
         MAX(Data1) FOR Question IN (' + @ques + ')
     ) AS PivotTable
 )
 SELECT (
     SELECT ' + @DummyColumns + ', la.*
     FROM LatestAnswer AS la
     FOR JSON PATH  
 ) AS my_json';

Property 'RID' cannot be generated in JSON output due to a conflict with another column name or alias. Use different names and aliases for each column in SELECT list.

解决方案 #

错误原因是同时选择了@DummyColumns（包含所有列的null值）和la.*（原结果列），导致列名重复。正确做法是判断原结果是否为空，为空则返回全null记录，否则返回原结果，避免重复列。

修改后的完整存储过程代码：

ALTER PROCEDURE [dbo].[GetVendorCriticalityInventory]
    @CategoryName VARCHAR(120) = null 
AS
BEGIN
    DECLARE @sql1 Nvarchar(Max);
    DECLARE @ques Nvarchar(max);
    DECLARE @DummyColumns NVARCHAR(MAX); -- 存储全null列定义

    DECLARE @CommonColumns NVARCHAR(MAX) = '
RID,
RequestID,
[Application/Vendor Name],
[Risk Identified],
[Application/Vendor Type],
[Date of the Request],
[IT Owner],
[Business Owner],
Requestor,
[App/Vendor Criticality],
ISOClause,
Submodule
';

    SELECT @ques = STRING_AGG(CAST(QUOTENAME(Question) AS varchar(MAX)),',') 
    FROM
        (select distinct Question 
         from TPMSMasterData 
         where TPMSCategoryName = @CategoryName) AS D

    -- 清理固定列的换行、空格，避免拆分出空值
    DECLARE @CleanCommonColumns NVARCHAR(MAX) = REPLACE(REPLACE(@CommonColumns, CHAR(10), ''), CHAR(13), '');
    SET @CleanCommonColumns = TRIM(@CleanCommonColumns);
    SET @CleanCommonColumns = REPLACE(@CleanCommonColumns, ', ', ',');

    -- 生成所有列的NULL AS [列名]定义
    SELECT @DummyColumns = STRING_AGG('NULL AS ' + TRIM(value), ', ')
    FROM STRING_SPLIT(@CleanCommonColumns + ',' + @ques, ',')
    WHERE TRIM(value) <> '';

    SET @sql1 = '

    WITH LatestData AS 
    (
        select
            R.RequestID as RID,
            R.RequestIDInfo as RequestID,
            isnull(R.NameOftheTool, ''N/A'') as [Application/Vendor Name],
            RiskSummaryScore as [Risk Identified],
            isnull(convert(varchar(10), R.RequestSubmitted, 120), ''N/A'') as [Date of the Request],
            isnull(R.NameOfModule, ''N/A'') as [Application/Vendor Type],
            ''N/A'' as [IT Owner],
            isnull(R.BusinessOwners, ''N/A'') as [Business Owner],
            isnull(R.RequestCreatedByEmailID, ''N/A'') as Requestor,
            isnull(cast(R.AppCriticalityScore AS varchar), ''N/A'') AS [App/Vendor Criticality],
            isnull(A.Question, ''N/A'') as Question,
            R.ISOClause,
            R.Submodule,
            isnull(case
                       when exists (SELECT 1 
                                    FROM STRING_SPLIT(A.DataControlLup, '':'') AS sg 
                                    WHERE TRIM(sg.value) = ''LookupTable'' ) 
                           then d.lookupDisplayValue 
                       when A.DataControlLup like ''%[A-Za-z]%'' 
                           then c.LupDisplayValue 
                       else b.Data1 
                   end, 
            ''N/A'') as Data1 ,
            row_number() over (partition by B.RequestID, A.Question order by B.TPMSTransID desc) as rn
        from
            TPMSMasterData A
        join
            TPMSTransactionData B on A.TPMSID = b.TPMSID
        join
            RequestInfo R on B.RequestID = R.RequestID
        left join
            BusinessLookupTable c on TRY_CAST(b.Data1 as int) = c.BusinessLupID
        left join 
            LookupTable d on try_cast(B.Data1 AS INT) = d.LookupID
        where
            A.TPMSCategoryName = @CategoryName 
            and R.WorkFlowCompleted = 1 

),
LatestAnswer AS (
    SELECT ' + @CommonColumns + ', ' + @ques + '
    FROM (
        SELECT ' + @CommonColumns + ',Question,Data1
        FROM LatestData 
        WHERE rn = 1
    ) as TPMSCategory
    pivot(max(Data1) for Question in (' + @ques + ') ) AS PivotTable
)
-- 优先返回原结果的JSON，无数据则返回全null记录的JSON数组
SELECT COALESCE(
    (SELECT * FROM LatestAnswer FOR JSON AUTO),
    (SELECT ' + @DummyColumns + ' FOR JSON PATH)
) AS [my_json]
';

exec sp_executesql @sql1,N'@CategoryName VARCHAR(120)',@CategoryName
END
GO

关键说明 #

• @CleanCommonColumns：清理固定列中的换行、空格，避免拆分时生成无效空值
• @DummyColumns：动态生成所有列的NULL AS [列名]定义，确保列名与原结果完全一致
• COALESCE函数：优先返回原查询的JSON结果（有数据时），若原结果为null（无数据），则返回全null记录的JSON数组

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