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

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?

CodePudding user response：

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"}]

CodePudding user response：

One solution is to use FOR JSON; you just need to build the appropriate select statement:

declare @val1 int = 1234;
declare @val2 varchar(100) = 'foobar';
declare @val3 datetime2(3) = sysdatetime();
declare @json nvarchar(max) = (
    select *
    from (values (@val1, @val2, @val3)) as v(val1, val2, val3)
    for json auto, without_array_wrapper
);
select @json;