CODEWITHSUNDEEP
sqlsql-serverjsont-sqlstored-procedures
JKennedy
JKennedy

Reputation: 18799

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

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?

Upvotes: 2

Views: 2164

Answers (1)

Mikhail Lobanov
Mikhail Lobanov

Reputation: 3026

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')

Upvotes: 4

Related Questions