SQL Server - 使用 JSON 返回列名 [英] SQL Server - Using JSON to return Column Names

问题描述

我有以下测试查询需要动态化.

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

基本上一个存储过程将被传递@json，它需要返回在该变量中传递的列名.

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

基本上@json 变量可以包含一个或多个或所有以下字段，甚至更多.

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"]'; 

推荐答案

在这种情况下(您的 json 字符串仅包含带有列名的标题)并且仅用于列选择，因此很容易.如果您也想从 json 字符串中获取值，那就更难了.

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.

变体 1: 使用系统表来验证您的输入并获取列名.

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

变体 2: 使用字符串.

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

这篇关于SQL Server - 使用 JSON 返回列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！