I have a problem with a stored procedure I want to write. In this SP I have a JsonInput and I have a value from a table in my database. I want to read the JsonInput and use the information to search for an value in my table. Then find the value and add it to my JsonOutput.
code looks like this:
declare @JsonInput nvarchar(MAX),
@JsonOutput nvarchar(MAX)
set @JsonInput= N'{"Year":2022,"Surname":"Axel","Land":"Asia"}'
select @JsonInput
--define local variables
declare @name nvarchar(50),
@year int,
@land nvarchar(50),
@value nvarchar(50)
-- get json input data for dataset
set @name = JSON_VALUE(@JsonInput,'$.Surname')
set @year = JSON_VALUE(@JsonInput,'$.Year')
set @land = JSON_VALUE(@JsonInput,'$.Land')
--get value
select @value = [Value] from dbo.myTable where ID='6' and [Year]=@year and Land=@land
select @value
set @JsonOutput = (
select *
from dbo.myTable where Surname=@name and [Year]=@year and Land=@land
for json auto)
Set @JsonOutput = JSON_MERGE(jsonColumn, '{"MyValue":"@value"}')
select @JsonOutput
Maybe you guys can help me to achieve my goal. The result should look like this:
'{"Year":2022,"Surname":"Axel","Land":"Asia","MyValue":"3" }'
in The error messege is following text: 'JSON_MERGE' is not a recognized built-in function name. but I dont know which other function I can use or to write it different Thanks a lot
