Using FOR JSON and CTE and store it in a variable

user_4562963 0
Viewed 1712

How can I generate a JSON in T-SQL using FOR JSON from a select statement that uses a Common Table Expression (CTE) and then store it in a local variable? Without a CTE it could look like this:

DECLARE @var NVARCHAR(MAX) = (
    SELECT  x.Val
    FROM (VALUES (1)) AS x(Val)
    FOR JSON AUTO
)
SELECT @var

But how ist the syntax if a CTE is in used like the following?

WITH y AS (
    SELECT  Val
    FROM (VALUES (1)) AS _(Val)
)
SELECT  x.Val, y.Val ValY
FROM (VALUES (1)) AS x(Val)
JOIN y ON y.Val = x.Val
FOR JSON AUTO

Surrounding with DECLARE @var NVARCHAR(MAX) = ( ... ) does not work.

common-table-expression tsql json
1 Answers

Might be something like this?

DECLARE @json NVARCHAR(MAX);

WITH y AS (
    SELECT  Val
    FROM (VALUES (1)) AS _(Val)
)
SELECT @json=
(
    SELECT  x.Val, y.Val ValY
    FROM (VALUES (1)) AS x(Val)
    JOIN y ON y.Val = x.Val
    FOR JSON AUTO
);

SELECT @json;

Additionally you can place a CTE as a sub-query instead of the "WITH". The CTE is not much more than "syntactic sugar" actually to avoid repeated sub-queries and to get your SQL better readable. You can use this for the same:

DECLARE @json NVARCHAR(MAX)=
(
    SELECT  x.Val, y.Val ValY
    FROM (VALUES (1)) AS x(Val)
    JOIN (
            SELECT  Val
            FROM (VALUES (1)) AS _(Val)
         ) y ON y.Val = x.Val
    FOR JSON AUTO
);

SELECT @json
user_50892040
Related