The problem should be quite simple:
I want to pass json to a SQL Server stored procedure.
Three examples:
{ "id": 1, "name": "Name 1", "comment": "Test comment" }
{ "id": 1, "name": "Name 2" }
{ "id": 1, "comment": null }
Is something like that possible? Or what are the alternatives using stored procedures for updates?
You can also query your data FROM OPENJSON(...) and filter with WHERE to select the value of each property. It does not return what does not exist.
declare @j nvarchar(max) = N'[{ "id": 1, "name": "Name 1", "comment": "Test comment" },
{ "id": 1, "name": "Name 2" },
{ "id": 1, "comment": null }]'
select
j.[key],
jsub.*
from openjson(@j) j
cross apply (
select *
from openjson(j.[value])
) jsub
| key | key | value | type |
+-----+---------+--------------+------+
| 0 | id | 1 | 2 |
| 0 | name | Name 1 | 1 |
| 0 | comment | Test comment | 1 |
| 1 | id | 1 | 2 |
| 1 | name | Name 2 | 1 |
| 2 | id | 1 | 2 |
| 2 | comment | | 0 |
Then you can use MERGE statement.
db<>fiddle
create procedure p_merge (@j nvarchar(max))
as
begin
merge into comments as t
using (
select
max(case [key] when 'id' then value end) as id,
max(case [key] when 'name' then value end) as name,
max(case [key] when 'comment' then value end) as comment,
max(case [key] when 'id' then flag end) as id_upd,
max(case [key] when 'name' then flag end) as name_upd,
max(case [key] when 'comment' then flag end) as comment_upd
from (
select j.*, 1 as flag
from openjson(@j) as j
) as p
) as s
on s.id = t.id
when matched then update
set name = case when s.name_upd = 1 then s.name else t.name end,
comment = case when s.comment_upd = 1 then s.comment else t.comment end
when not matched then insert
values(s.id, s.name, s.comment)
;
end;