Store variable SQL Statement into a JSON variable

Question

I want to store a FOR JSON AUTO output in a variable.

But I want to use a variable select statement.

Something like this:

SET @SQLString = 'SELECT ID, Name FROM ' + @TableName + ' ';

SET @Body = (
   @SQLString + 'FOR JSON AUTO'
);

Should be able to do something like dbfiddle.uk/jX2avhmt - be careful to avoid SQL injection when making it dynamic — Martin Smith
– Martin Smith, Commented Mar 10, 2024 at 15:11

David Browne - Microsoft · Accepted Answer · 2024-03-10 15:14:34Z

1

Like this

declare @sql nvarchar(max) = 'select * from sys.objects'

declare @forJsonSql nvarchar(max) = concat('select @json = (',@sql, ' for json auto)')

--print @forJsonSql

declare @json nvarchar(max)
exec sp_executesql @forJsonSql, N'@json nvarchar(max) output', @json=@json output

select @json

answered Mar 10, 2024 at 15:14

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

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

Collectives™ on Stack Overflow

Store variable SQL Statement into a JSON variable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related