clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335789 fiddles created (27476 in the last week).

-- https://dba.stackexchange.com/questions/295650/handling-optional-parameters-in-stored-procedure
 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" } '; 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; @_a int,@_b varchar(10),@_c varchar(10)
a b c
1 foo
2 bar init
 hidden batch(es)


select * from foo;
a b c
1 foo
2 bar init
 hidden batch(es)