I have a stored proc say sp_proc_1
which takes in a few parameters say @val1
, @val2
, and @val3
. I would like to make a JSON object using the values @val1
and @val2
so that I can pass that JSON object as one of the parameters to a second stored procedure sp_proc2
which I will be invoking from sp_proc1
. Please guide.
The way I think is to insert @val1
and @val2
into a temp_table
and then select them from the temp_table
into a JSON object. Am I thinking right?
Advertisement
Answer
The statement depends on the format of the expected JSON output, but a possible option is FOR JSON PATH
:
CREATE PROCEDURE Proc_1 @val1 int, @val2 varchar(100), @val3 datetime AS BEGIN DECLARE @json nvarchar(max) SET @json = (SELECT @val1 AS val1, @val2 AS val2 FOR JSON PATH) EXEC Proc_2 @json END
The generated JSON is:
[{"val1":1,"val2":"Test value"}]