SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- https://dba.stackexchange.com/questions/295650/handling-optional-parameters-in-stored-procedure
declare 
    @payload1 nvarchar(255),
    @payload2 nvarchar(255);

set @payload1 = N'
{
    "a":1,
    "b":"foo",
    "c":null
}
';
set @payload2 = N'
{
    "a":2,
    "b":"bar"
}
';

select 
   'payload1' as which,
   j.[key],
   j.[value],
   j.[type]
from openjson(@payload1, '$') AS j
union all
select 
   'payload2' as which,
   j.[key],
   j.[value],
   j.[type]
from openjson(@payload2, '$') AS j;


which
key
value
type


payload1
a
1
2

payload1
b
foo
1

payload1
c
null
0

payload2
a
2
2

payload2
b
bar
1


create table foo (
    a int not null primary key,
    b varchar(10),
    c varchar(10)
);

insert into foo (a,b,c)
values
(1,'init','init'),
(2,'init','init');
2 rows affected

select a,b,c
from foo;

-- select object_id(N'dbo.foo')


a
b
c


1
init
init

2
init
init


create proc Test 
    @payload nvarchar(255),
    @echo bit = 1,
    @execute bit = 0
as
begin
    declare 
        @sql nvarchar(1000) = N'update dbo.foo set '+nchar(10),
        @params_schema nvarchar(1000) = N'';

    declare 
        @a int,
        @b varchar(10),
        @c varchar(10);

    drop table if exists #col_cache;
    select 
        c.column_id,
        c.[name],
        p.[key],
        p.[value],
        p.[type] as j_type,
        t.[name] as data_type,
        t.max_length,
        t.precision,
        t.scale,
        case
            when t.[name] in (
                                N'time',
                                N'datetime2',
                                N'datetimeoffset',
                                N'smalldatetime',
                                N'datetime',
                                N'varbinary',
                                N'varchar',
                                N'binary',
                                N'char',
                                N'nvarchar',
                                N'nchar'
                             )
                then N'('+replace(convert(nvarchar(10),c.max_length),N'-1',N'MAX')+ N')'
            when t.[name] in (N'decimal',N'numeric')
                then N'('+convert(nvarchar(10),c.precision)+N','+convert(nvarchar(10),c.scale)+N')'
            else
                N''
        end as type_suffix
    into #col_cache
    from sys.columns c
    outer apply (
        select 
            j.[key],
            j.[value],
            j.[type]
        from openjson(@payload, '$') as j
        where j.[key] = c.[name] collate database_default
    ) as p
    left join sys.types as t on t.user_type_id = c.user_type_id
    where c.[object_id] = object_id(N'dbo.foo') 
      and c.is_identity = 0;

    with kvp as (
        select 
            [key],
            [value]
        from #col_cache
    )
    select 
        @a = a,
        @b = b,
        @c = c
    from kvp
    pivot (
        max([value])
        for [key] in (a,b,c)
    ) p;
    

    select 
        @sql += string_agg(
            N'   '
            + quotename(cc.[name]) 
            + N' = '
            + iif(
                cc.[key] is null, 
                quotename(cc.[name]),
                N'@_' + cc.[name]
              ), 
            nchar(44) + nchar(10)
        ),
        @params_schema += string_agg(
            N'@_' 
            + cc.[name]
            + N' '
            + cc.[data_type]
            + cc.type_suffix,
            nchar(44)
        )
    from #col_cache as cc;
    
    select @sql += nchar(10) + N'where a = @_a;';

    if @echo = 1
    begin
        select 
            @sql as [sql],
            @params_schema as params_schema;
    end
    if @execute = 1
    begin
        exec sp_executesql 
            @sql,
            @params_schema,
            @a,@b,@c;
    end
end
declare 
    @payload1 nvarchar(255),
    @payload2 nvarchar(255);

set @payload1 = N'
{
    "a":1,
    "b":"foo",
    "c":null
}
';
set @payload2 = N'
{
    "a":2,
    "b":"bar"
}
';

exec Test @payload1;
exec Test @payload2;


sql
params_schema


update dbo.foo set 
   [a] = @_a,
   [b] = @_b,
   [c] = @_c
where a = @_a;
@_a int,@_b varchar(10),@_c varchar(10)




sql
params_schema


update dbo.foo set 
   [a] = @_a,
   [b] = @_b,
   [c] = [c]
where a = @_a;
@_a int,@_b varchar(10),@_c varchar(10)


exec Test N'{"a":1,"b":"foo","c":null}', @execute = 1;
exec Test N'{"a":2,"b":"bar"}', @execute = 1;

select * from foo;


sql
params_schema


update dbo.foo set 
   [a] = @_a,
   [b] = @_b,
   [c] = @_c
where a = @_a;
@_a int,@_b varchar(10),@_c varchar(10)




sql
params_schema


update dbo.foo set 
   [a] = @_a,
   [b] = @_b,
   [c] = [c]
where a = @_a;
@_a int,@_b varchar(10),@_c varchar(10)




a
b
c


1
foo
null

2
bar
init


select * from foo;


a
b
c


1
foo
null

2
bar
init
which	key	value	type
payload1	a	1	2
payload1	b	foo	1
payload1	c	null	0
payload2	a	2	2
payload2	b	bar	1