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
0
payload2
a
2
2
payload2
b
bar
1
…
hidden batch(es)
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
hidden batch(es)
select a,b,c
from foo;
-- select object_id(N'dbo.foo')
a
b
c
1
init
init
2
init
init
…
hidden batch(es)
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
✓
hidden batch(es)
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)
…
hidden batch(es)
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;
