将动态sql转换为T-sql中的键值对

Question

在存储过程中，我需要将存储在变量@SQL中的动态SQL语句转换为一个基本上由键值对组成的临时表-对@SQL的内容一无所知，只知道它是一个选择语句、对表值函数的调用或其他将返回结果集的SQL。换句话说，在调用存储过程时，该选择的模式不是预定义的或已知的。

所以，本质上，我需要能够转换

exec sp_executesql @sql

其中提供的特别查询可以是类似的东西

select foo_varchar, bar_int, alice_bit from [some_table] with (nolock)

转换为形式的临时表(其中field_value是任何值的varchar表示(：

row_num  field_name   field_value
-------  ----------   --------------
1        foo_varchar  'a funny name'
1        bar_int      '17'
1        alice_bit    '1'
2        foo_varchar  'another name'
2        bar_int      '76'
2        alice_bit    '0'
...

到目前为止，我一直无法想出一种方法来做到这一点。但在我放弃并尝试不同的方法之前，我想我应该问问我的同事。。。

Answer 1

不需要动态SQL。您可以使用一点JSON 动态取消数据透视

示例或dbFiddle

Select A.row_num
,B.*
From  (
Select *,row_num = row_number() over ( order by (select null))
From  YourTable
) A
Cross Apply (
Select field_name  = [Key]
,field_value = [Value]
From OpenJson((Select A.* For JSON Path,Without_Array_Wrapper,INCLUDE_NULL_VALUES  ) ) 
Where [Key] not in ('row_num')
) B

注意：

• ,INCLUDE_NULL_VALUES是可选的
• order by (select null)是一个通用的占位符。如果可用，请使用正确的顺序
• JSON方法需要2016+。对于<2016

更新-用于全动态选项

-- Your ORIGINAL SQL
Declare @SQL varchar(max) = 'Select foo_varchar,bar_int,alice_bit from YourTable '
-- Here we Augment the Original SQL
-- You may want to consider a scalar-valued function where you pass `@SQL`
Set @SQL = '
Select A.row_num
,B.*
From  (
Select *,row_num = row_number() over ( order by (select null))
From  ('+@SQL+') src
) A
Cross Apply (
Select field_name  = [Key]
,field_value = [Value]
From OpenJson((Select A.* For JSON Path,Without_Array_Wrapper,INCLUDE_NULL_VALUES  ) ) 
Where [Key] not in (''row_num'')
) B
'
Exec(@SQL)

Answer 2

请求的XML版本或dbFiddle

Select A.row_num
,C.*
From  (
Select *,row_num = row_number() over ( order by (select null))
From  YourTable
) A
Cross Apply ( values ((Select A.* for XML RAW,Type)) )B(XMLData)
Cross Apply (
Select field_name  = xAttr.value('local-name(.)', 'varchar(100)')
,field_value  = xAttr.value('.','varchar(max)')
From XMLData.nodes('//@*') xNode(xAttr)
Where xAttr.value('local-name(.)', 'varchar(100)')  not in ('row_num')
) C

全动态版本

Declare @SQL varchar(max) = 'Select foo_varchar,bar_int,alice_bit from YourTable '
-- Augment Original SQL
Set @SQL = '
Select A.row_num
,C.*
From  (
Select *,row_num = row_number() over ( order by (select null))
From  ('+@SQL+') src
) A
Cross Apply ( values ((Select A.* for XML RAW,Type)) )B(XMLData)
Cross Apply (
Select field_name  = xAttr.value(''local-name(.)'', ''varchar(100)'')
,field_value  = xAttr.value(''.'',''varchar(max)'')
From XMLData.nodes(''//@*'') xNode(xAttr)
Where xAttr.value(''local-name(.)'', ''varchar(100)'')  not in (''row_num'')
) C
'
Exec(@SQL)

EDIT-如果打开表值函数

Declare @SQL varchar(max) = 'Select foo_varchar,bar_int,alice_bit from YourTable '
-- Augment Original SQL
Set @SQL = '
Select A.row_num
,B.*
From  (
Select *,row_num = row_number() over ( order by (select null))
From  ('+@SQL+') src
) A
Cross Apply [dbo].[tvf-XML-UnPivot-Row]((Select A.* for XML RAW)) B
Where Item<>''row_num''
'
Exec(@SQL)

感兴趣的功能

CREATE FUNCTION [dbo].[tvf-XML-UnPivot-Row](@XML xml)
Returns Table 
As
Return ( 
Select Item  = xAttr.value('local-name(.)', 'varchar(100)')
,Value = xAttr.value('.','varchar(max)')
From  @XML.nodes('//@*') xNode(xAttr)
)