Is It possible to get JSON as an out parameters using sp_executesql

Viewed 1394

I am writing a stored procedure to try and track changes to objects in different tables.

I am trying to construct SQL dynamically so am forced to use sp_executesql

So far I have:

SET @SQLInserted = 'Select * from ' + '[Diary.Day]' + ' Where Id=' + '13'+  ' for json path';
SET @SQLClash = 'Select * from ' + '[Diary.Day]' + ' Where Id=' + '12'+  ' for json path';

Where the [Diary.Day] and [Id] are parameters passed into the stored procedure.

I can then use sp_executesql to run this sql and show a JSON object.

But I would not like to put that object into a variable to be able to insert it into a table.

So far I have:

DECLARE @jsonInserted NVARCHAR(MAX), @jsonClash NVARCHAR(MAX)

DECLARE @SQLInserted nvarchar(500),  @SQLClash nvarchar(500);
SET @SQLInserted = 'Select * from ' + '[Diary.Day]' + ' Where Id=' + '13'+  ' for json path';
SET @SQLClash = 'Select * from ' + '[Diary.Day]' + ' Where Id=' + '12'+  ' for json path';

exec sp_executesql @SQLInserted
exec sp_executesql @SQLClash

Insert Into [Log.Transaction] ([Table], [EntryTimeRaw], [OldObject], [NewObject], [Operation]) 
Values ('[Diary.Day]', 10000000, @jsonInserted, @jsonClash, 'CLASH')

but obviously the @jsonInserted and @jsonClash variables are not being assigned so the above SQL does not insert anything.

Is it possible to assign JSON as an out paramerter when using sp_executesql or have I done something wrong?

1 Answers
Related