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

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

  • Auto increment a bigint column?
  • Is it possible to define a local function in a TSQL query?
  • convert decimal to time 6.80 =7.20 hrs in SQL
  • Stored procedure in SQL Server (order by desc)?
  • Rollback and Raiseerror, which first?
  • ddmmyyyy to sql datetime in SQL
  • 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?

    One Solution collect form web for “Is It possible to get JSON as an out parameters using sp_executesql”

    I’m not tested this example because I have SQL 2014.
    Result should be like this

    DECLARE @jsonInserted NVARCHAR(MAX), @jsonClash NVARCHAR(MAX)
    
    DECLARE @SQL NVARCHAR(500)
    SET @SQL = N'SET @json = (SELECT * FROM [Diary.Day] WHERE Id = @Id FOR JSON PATH)';
    
    EXEC sp_executesql @SQL, N'@id INT, @json NVARCHAR(MAX) OUTPUT', @id = 12, @json = @jsonInserted OUTPUT
    EXEC sp_executesql @SQL, N'@id INT, @json NVARCHAR(MAX) OUTPUT', @id = 13, @json = @jsonClash OUTPUT
    
    INSERT INTO [Log.Transaction] ([Table], [EntryTimeRaw], [OldObject], [NewObject], [Operation]) VALUES ('[Diary.Day]', 10000000, @jsonInserted, @jsonClash, 'CLASH')
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.