Passing dynamic column names an data in json to query with

1

I have dynamic queries coming from the front end, and in these scenarios I very strongly want to use parameterized sql for dynamic queries(sp_executesql)

declare @json varchar(300) = ‘[{ “col” : “col1”, “val” : “test” },{ “col” : “col2”, “val” : “test88” },...]

From above json i want to dynamically filter my table where col1 = ‘test’ and col2...

This can be completly dynamic so there can be 1 or 50 filters and any columns.

The only solution i can think of is compile dynamic sql by looping through json and use @param1 through @param50 and set each param in order

2

So this json's content is dynamic?

3

Oopa sorry skipped right over this part

4

This is a rough example of the method I described... Which I hate!:slight_smile:

    declare @json varchar(max) = '{"customFilters":[null,{"text":"12321321","index":1,"filter":{"icon":"equal","tooltip":"equal to"},"selectedHeader":"umRaceEthnicity"},{"text":"2131312","index":2,"filter":{"icon":"equal","tooltip":"equal to"},"selectedHeader":"umDOB"}]}'
select * 
into #tmp
from openjson(@json,'$.customFilters') a
where  not a.value is null
--immidiate
declare @sql varchar(max) = 'select 1 from myTable a where 1=1'
declare @startKey int = 0
declare @param1 varchar(100),
@param2 varchar(100),
@param3 varchar(100)
while (exists(select * from #tmp a where a.[key] > @startKey))
begin
	set @startKey = @startKey + 1
	declare @jsonVal varchar(max) = (select [value] from #tmp a where a.[key] = @startKey)
	select @jsonVal
	set @sql = concat(@sql,' and ','[',json_value(@jsonVal,'$.selectedHeader'),']','=','@param',@startKey)
	if(@startKey =1)
	begin
		set @param1 = json_value(@jsonVal,'$.text')
	end
	else if(@startKey =2)
	begin
		set @param2 = json_value(@jsonVal,'$.text')
	end
	else if(@startKey =3)
	begin
		set @param3 = json_value(@jsonVal,'$.text')
	end
end 
select @sql
 exec sp_execute @sql,@param1,@param2,@param3
5

this looks disgusting and probably very dangerous

;with j1
as
(
	SELECT *, 'myTable' as tablename
	 FROM OPENJSON ( @json ,'$.customFilters')  a
	 where not a.value is null
),
j2 as (
	select column_name , column_value, tablename
	  from j1
  
	 cross apply OPENJSON(j1.value)
	  WITH (   
				column_value   VARCHAR(200)   '$.text',
				column_name VARCHAR(200)   '$.selectedHeader'
		  )
)
select 'select 1 from myTable a where 1 = 1  ' + 
STUFF((SELECT distinct ' and ' + 
			   case when b.column_name is null 
                                or b.column_value is null then ' ' 
			   else c.name + ' = ' + b.column_value
			   end
          FROM j2 b
		  join sys.tables t on t.name = b.tablename
		  join sys.columns c on c.name = b.column_name
          WHERE a.tablename = b.tablename
          FOR XML PATH('')), 1, 1, '') query

       
  from j2 a  
 group by a.tablename