JSON data and a T-SQL variable.

G’day,

You may have noticed when looking at FOR JSON AUTO or FOR JSON PATH that both clauses result in one single column that contains a JSON string.

USE tempdb;
GO
SELECT 
*
FROM
sys.configurations AS C
ORDER BY
C.[configuration_id]
FOR JSON AUTO;
/*
Or
*/SELECT 
*
FROM
sys.configurations AS C
ORDER BY
C.[configuration_id]
FOR JSON PATH;

But, what if we wanted this data to be put directly into a T-SQL variable?

For a standard T-SQL Statement, we might use something of the form

DECLARE @ConfigurationName SYSNAME = N'';
SELECT 
@ConfigurationName = C.[name]
FROM
sys.configurations AS C
WHERE
C.configuration_id = 101;
PRINT @ConfigurationName;

The WHERE clause in the above statement ensures that only one row is brought back for [name] and everything is fine.

As an aside, consider what would happen in the above statement if we omitted the WHERE clause (and it brought back multiple rows of data)

Yes, we’d bring back more than a single value, but would we expect to get an error?

Try it

DECLARE @ConfigurationName SYSNAME = N'';
SELECT 
@ConfigurationName = C.[name]
FROM
sys.configurations AS C
PRINT @ConfigurationName;

Notice that this time we get the last value in the returned list of names. On my system that is ‘allow polybase export’ and not ‘recovery interval (min)’ as in the initial example when we had a WHERE clause.

The lesson here is if you expect a single value then ensure that you are only getting a single value back.

But that is a different discussion for a different day.

Right now back to JSON (where the statement guarantees us a single value)

So we have a query that returns a JSON string? So how do we get that string into a variable? we know that there is only one item coming back – however, we can’t access that column directly – so we have to get creative! (or maybe not that creative depending on your T-SQL knowledge)

DECLARE @JSON NVARCHAR(MAX) = N'';
SELECT @JSON = (SELECT 
*
FROM
sys.configurations AS C
ORDER BY
C.[configuration_id]
FOR JSON AUTO);
PRINT @JSON
GO
/*
OR
*/DECLARE @JSON NVARCHAR(MAX) = N'';
SELECT @JSON = (SELECT 
*
FROM
sys.configurations AS C
ORDER BY
C.[configuration_id]
FOR JSON AUTO);
PRINT @JSON
GO

And there we have it. Now we have a piece of JSON that we have gained directly from a SQL Server table placed straight into a T-SQL variable;

I hope this has helped on your SQL Server JSON journey. We’ll see more real soon.

Have a great day

Cheers

Marty

Download Files

JSON data and a T-SQL variable.