SQL statement to return Null values for multiple columns if no rows found

Question

I have a problem coming up with a way to solve this problem. Namely, I want the second SELECT statement to return null values even if the row is not found using the WHERE clause.

I tried many different solutions, but nothing worked (using IF, CTE, etc.). Can you please help?

    SELECT  RA.id
        ,AllEngagementsAndJobCodes =
        (
            SELECT 
            (
                SELECT
                (...)

                For JSON Path
            ) AS  engagements
            ,
            (
                SELECT [someColumn] as [id], [someColumn2] as [name]
                FROM [someInterface].[someSchema].[someTable]
                WHERE [someID] = '12345'

                For JSON Path, INCLUDE_NULL_VALUES
            ) as jobCodes
        For JSON Path, WITHOUT_ARRAY_WRAPPER
        )

The output of this should be one column (which is trivial for this question) and the second column containing the JSON path, which should look something like this:

    {
        "engagements": [
            {
                //results of the first query, not siginificant for the question
            }
        ],
        "jobCodes": [
            {
                "id": null,
                "name": null
            }
        ]
    }

Note that if the query DOES return something, it might be more than one row and all of them need to be included in the output JSON.

You should probably provide a working query, sample data, current and expected results to clarify your requirement. — GMB
– GMB, Commented Feb 5, 2020 at 9:41
I'll use a LEFT JOIN with the same table, with no conditions on the LEFT component and COALESCE for the right elements. If you provide a working sample, I can try to rewrite the query this way. — Nicola Lepetit
– Nicola Lepetit, Commented Feb 5, 2020 at 9:48

Zohar Peled · Accepted Answer · 2020-02-05 11:11:11Z

1

One way to do it is using a common table expression and union all:

;WITH CTEInnerQuery AS
(
    SELECT [someColumn] as [id], [someColumn2] as [name]
    FROM [someInterface].[someSchema].[someTable]
    WHERE [someID] = '12345'
), CTEInnerJson AS
(
    SELECT *
    FROM CTEInnerQuery
    UNION ALL
    SELECT NULL as [id], NULL as [name]
    WHERE NOT EXISTS(SELECT 1 FROM CTEInnerQuery)
)

SELECT  RA.id
    ,AllEngagementsAndJobCodes =
    (
        SELECT 
        (
            SELECT
            (...)

            For JSON Path
        ) AS  engagements
        ,
        (
            SELECT *
            FROM CTEInnerJson
            For JSON Path, INCLUDE_NULL_VALUES
        ) as jobCodes
    For JSON Path, WITHOUT_ARRAY_WRAPPER
    )

edited Feb 5, 2020 at 11:11

answered Feb 5, 2020 at 9:45

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

xLokos Over a year ago

When I try to run this, I get the error:

The FOR XML and FOR JSON clauses are invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table or common table expression or view and apply FOR XML or FOR JSON on top of it.

and when I wrap the inner query in another SELECT, I get this error:

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Zohar Peled Over a year ago

Sorry, I've edited my answer. Please try the updated code.

xLokos Over a year ago

It works! Thank you so much, I spent 2 days trying to figure this out..

Zohar Peled Over a year ago

Glad to help :-)

Nguyễn Văn Phong · Accepted Answer · 2020-02-05 09:45:21Z

0

You can use this trick the resolve your problem

IF(NOT EXISTS (...))
BEGIN
    SELECT  0 AS UserID
            ,N'' UserName
    WHERE 1 = 2

    RETURN;
END

answered Feb 5, 2020 at 9:45

Nguyễn Văn Phong

14.2k19 gold badges47 silver badges65 bronze badges

Collectives™ on Stack Overflow

SQL statement to return Null values for multiple columns if no rows found

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related