Create a JSON object to be sent as parameter to Stored Procedure

February 17, 2022

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?

>Solution :

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: