|
|
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
|
