Hello,
Is it possible to return a json object without repeating code?
I have a procedure that the scalar value always returns a json object.
If I save the json to a nvarchar variable, I lose that "reference". Example "JSON_F52E2B61-18A1-11d1-B105-00805F49916B"
The scalar result seems that needs to be an "FOR JSON" syntax in order to work with the app that calls that procedure.
An example would be something like this:
SET NOCOUNT ON;
DECLARE @json nvarchar(MAX);
DECLARE @data TABLE(someint int, somevalue varchar(80));
INSERT INTO @data(someint, somevalue)
VALUES(1,'A'), (1,'B'), (1,'C'), (2,'D'), (3,'E'), (3,'F'), (4,'G'), (5,'H')
DECLARE @jsonlog TABLE(generatedfile nvarchar(MAX));
SET @json = (
SELECT x.someint, COUNT(*) AS cnt,
JSON_QUERY((SELECT somevalue FROM @data WHERE someint = x.someint FOR JSON PATH)) AS items
FROM @data x
GROUP BY x.someint
FOR JSON PATH
);
-- Keep log of generated file
INSERT INTO @jsonlog(generatedfile) VALUES(@json);
-- return JSON object to app-client (stored procedure)
SELECT @json AS ' ' FOR JSON PATH, WITHOUT_ARRAY_WRAPPER -- something like this? the json is not the same of course
--SELECT JSON_QUERY(@json) -- does not work


