SQL Server - Using JSON to return Column Names

Question

I have the following test query I'm needing to make dynamic.

Basically a stored procedure will be passed @json and it needs to return the column names that are passed in that variable.

Is this possible and how could I do it?

declare @json varchar(max)

set @json = '["FirstName", "LastName","DOB"]';  

select *   
from OPENJSON( @json )  

select
FirstName,
LastName,
DOB
from Client

I do have this that works, but not sure on whether it's a good option and whether there's a better way

declare @json varchar(max)
declare @columnames varchar (200)
declare @sqlquery nvarchar(200)

set @json = '["FirstName", "LastName","DOB"]';  
set @columnames =''

select @columnames = 
    case when @columnames = ''
    then value
    else @columnames + coalesce(',' + value, '')
    end
  from OPENJSON( @json )  

set  @sqlquery = 'select ' + @columnames + ' from Client'

EXEC SP_EXECUTESQL @sqlquery

Basically the @json variable can contain one or many or all of the below fields and over-time even more.

set @json = '["FirstName", "LastName","DOB","DrugName,"Age","AgeGroup","Overdose","VerificationCode","Gender"]';

Best way is parse data in application. Best practice on sqlserver is create CLR TVL function with deserialization - this function in non-deterministic and you have to use generic code in your app. In this json example string you can use working with strings, but you can have much more complicated strings include escaping characters. Conclusion - parse data in app. Second option - use serialization with scalar CLR to xml and use xquery on sqlserver. Example: sqlservercentral.com/articles/SQLCLR/74160 — Deadsheep39
– Deadsheep39, Commented Jan 2, 2017 at 9:03
Wow, that's some complex stuff there @Deadsheep39. The reason I'm needing to do this is that it's a custom report I'm building where the user on the front-end needs the ability to select the columns they wish to return in the report. — Philip
– Philip, Commented Jan 2, 2017 at 9:15
I don't know that it has to be as complicated as @Deadshepp39 implies. Certainly I would also suggest parsing on the application side. That is return all columns and decide what to show in the app side. There is more data traffic but less to go wrong with dynamic SQL (in particular, sql injection). The dynamic SQL you have is the only solution for this but you are right to be suspicious of it. It's best avoided. SQL Server works best when it knows exactly what columns are being returned all the time. — Nick.Mc
– Nick.Mc, Commented Jan 2, 2017 at 9:49
Thanks for your thoughts @Nick.McDermaid When are you say passed on the application side what are you referring to, as I'm not sure what would be passed to the stored procedure? — Philip
– Philip, Commented Jan 2, 2017 at 9:51
Based on your example: Your application runs SELECT field1,field2,field3 FROM Table (i.e. every field). Then inside the application it decides (based on your user selection) what is actually shown. That's what I mean by parsed. There is probably more to this than you've explained though. — Nick.Mc
– Nick.Mc, Commented Jan 2, 2017 at 9:54

Deadsheep39 · Accepted Answer · 2017-01-02 16:46:30Z

In this case (your json string contain only header with column names) and used for column selection only so it's easy. It's much more harder if you would like get values from json string too.

Variant 1: Use system table to validate your input and get column names.

declare @json varchar(800) = '["FirstName", "LastName", "DOB","DrugName,"Age","AgeGroup", "Overdose","VerificationCode","Gender"]',
@columnames varchar(800)

select @columnames = isnull(@sql + ', ', name) + name
from (
    select name, '"' + name + '"' name_quoted
    from sys.columns c
    where object_id('dbo.Client') = object_id) t
where @json like '%' + name_quoted + '%'

print @columnames
exec ('select ' + @columnames + ' from dbo.Client')

Variant 2: Working with strings.

declare @json varchar(800) = '["FirstName", "LastName","DOB","DrugName,"Age","AgeGroup","Overdose","VerificationCode","Gender"]';
declare @sql varchar(max) = replace(substring(@json, 2, len(@json) - 2), '"','')
print @sql
exec ('select ' + @sql + ' from dbo.Client')

Collectives™ on Stack Overflow

SQL Server - Using JSON to return Column Names

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related