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?