As a workaround approach: if you can modify the caller code to supply a document instead, you can both
- detect if a key/column exists in the supplied document to omit updates where a key is not present and
- allow for set-null commands to valid keys
Consider the following (slightly bulky) POC (full db<>fiddle)
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
Note the above pattern is limited to a single table pattern of the form...
create table foo (
a int not null primary key,
b varchar(10),
c varchar(10)
);
...and requires modification for each table definition against which it might be applied (unless you fancy nesting dynamic SQL (which I don't)). The approach is similar to the catch-all where clause solution described in greater detail by Erland Sommarskog and Aaron Bertrand.
By way of local example. If our table dbo.foo
had the following, data...
a |
b |
c |
1 |
init |
init |
2 |
init |
init |
...an execution of the following commands...
exec Test N'{"a":1,"b":"foo","c":null}', @execute = 1;
exec Test N'{"a":2,"b":"bar"}', @execute = 1;
...would result in the following data...
a |
b |
c |
1 |
foo |
NULL |
2 |
bar |
init |