I have a stored procedure where I get some column names from the table and take remaining column names dynamically. I get column names with no records if I don't convert it to JSON string but if I convert it to json_string using JSON AUTO it returns null instead of all column names with one null record. How to get all column name with one null record here?

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 = '
    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

The output I get when there are no record to display is

I have a stored procedure where I get some column names from the table and take remaining column names dynamically. I get column names with no records if I don't convert it to JSON string but if I convert it to json_string using JSON AUTO it returns null instead of all column names with one null record. How to get all column name with one null record here?

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 = '
    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

The output I get when there are no record to display is

But the output I want is

This is my latest try

 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';

And I get this error:

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.

How can I solve this duplicacy error?

• sql
• sql-server
• stored-procedures

• 2 If you get a NULL it means there were no results. No results is not the same as 1 row with NULL values. This dynamic SQL query is almost impossible to read. Have you inspected to see what @sql1 contains? Have you tried running that query to see if it returns anything? – Panagiotis Kanavos Commented yesterday
• 1 you can probably change your code to: monstrous sql goes here...FROM (select 1 as dummy) x LEFT JOIN LatestData ON LatestData.RN = 1...monstrous sql pt2 – siggemannen Commented yesterday
• 3 No it's not, @tgolisch , the OP is correctly parametrising it. – Thom A Commented yesterday
• Since it's not generating a syntax error we can probably assume that @CategoryName is matching enough rows to generate column names in @ques. Are there any matching rows in TPMSTransactionData and RequestInfo that would satisfy the join conditions? Any rows with R.WorkFlowCompleted=1? – AlwaysLearning Commented yesterday
• @AlwaysLearning there are records in TPMSMasterData , that is why its not causing any issues .when i have records in TPMSTransactionData i don't get any issue only when it is empty my problem starts i get null as string output instead of empty record with all column names – Sapri s Commented 23 hours ago

1 Answer 1

You can just left join (or OUTER APPLY) your resultset to a dummy row to guarantee at least one row in the results.

You also need to add INCLUDE_NULL_VALUES otherwise you get an array with one empty object.

'
    -- etc
    where A.TPMSCategoryName = @CategoryName AND R.WorkFlowCompleted=1 
),
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 + '
        )
)
SELECT (
    SELECT la.*
    FROM (VALUES (1) ) AS v(dummy)
    OUTER APPLY LatestAnswer AS la
    FOR JSON PATH, INCLUDE_NULL_VALUES
) AS my_json;
';

Note that dynamic SQL should be in nvarchar(max) not varchar(max).