如何将动态SQL存储过程结果与其他SQL查询关联？

我有一个包含动态SQL的存储过程，传入FormId后会返回对应表单的列及其值。由于每个表单的列数量和值都不同，无法使用临时表存储该存储过程的结果。请问如何将此存储过程的结果与其他SQL查询进行关联？

现有存储过程 #

declare @FormId tinyint = 1
declare @columns as varchar(max) = (select string_agg(quotename(ColumnName), ',') 
                                    from Paint_Form as f 
                                    inner join Paint_FormColumn as fc on fc.FormId = f.FormId 
                                    where f.FormId = @FormId)

declare @sql nvarchar(max) = '

select * 
from 
    (select 
         f.FormId,
         fc.ColumnName,
         fce.CellValue
     from  
         Paint_Form as f 
     inner join 
         Paint_FormColumn as fc on fc.FormId = f.FormId
     inner join 
         Paint_FormCell as fce on fce.ColumnId = fc.ColumnId
     where 
         f.FormId = ' + CONVERT(VARCHAR(4), @FormId) + ') t
pivot
(
    max(CellValue) for ColumnName in (' + @columns + ')
) p
'

exec (@sql)

期望实现的关联逻辑 #

select * 
from report 
left join storedProcedure on formid

由于存储过程返回的列是动态生成的，常规JOIN语法无法直接关联，推荐以下几种可行方案：

方案1：将关联逻辑嵌入动态SQL #

既然存储过程本身依赖动态SQL生成结果，直接把与report表的关联逻辑整合到动态查询中，一次性生成最终结果。

修改后的代码示例：

declare @FormId tinyint = 1
declare @columns as varchar(max) = (select string_agg(quotename(ColumnName), ',') 
                                    from Paint_Form as f 
                                    inner join Paint_FormColumn as fc on fc.FormId = f.FormId 
                                    where f.FormId = @FormId)

declare @sql nvarchar(max) = '
select r.*, p.*
from report r
left join (
    select 
         f.FormId,
         fc.ColumnName,
         fce.CellValue
     from  
         Paint_Form as f 
     inner join 
         Paint_FormColumn as fc on fc.FormId = f.FormId
     inner join 
         Paint_FormCell as fce on fce.ColumnId = fc.ColumnId
     where 
         f.FormId = ' + CONVERT(VARCHAR(4), @FormId) + '
) t
pivot
(
    max(CellValue) for ColumnName in (' + @columns + ')
) p on r.FormId = p.FormId
'

exec (@sql)

这是最直接的方案，避免了中间存储结果的麻烦，直接生成包含关联数据的动态查询。

方案2：动态创建临时表存储结果 #

可以通过动态SQL创建匹配结果结构的临时表，再进行关联：

declare @FormId tinyint = 1
declare @columnsDef as varchar(max) = (select string_agg(quotename(ColumnName) + ' varchar(max)', ', ') 
                                    from Paint_Form as f 
                                    inner join Paint_FormColumn as fc on fc.FormId = f.FormId 
                                    where f.FormId = @FormId)
declare @columnsPivot as varchar(max) = (select string_agg(quotename(ColumnName), ',') 
                                    from Paint_Form as f 
                                    inner join Paint_FormColumn as fc on fc.FormId = f.FormId 
                                    where f.FormId = @FormId)

-- 动态创建临时表并插入结果
declare @createTempSql nvarchar(max) = '
create table #FormResults (FormId tinyint, ' + @columnsDef + ')
insert into #FormResults
select * 
from 
    (select 
         f.FormId,
         fc.ColumnName,
         fce.CellValue
     from  
         Paint_Form as f 
     inner join 
         Paint_FormColumn as fc on fc.FormId = f.FormId
     inner join 
         Paint_FormCell as fce on fce.ColumnId = fc.ColumnId
     where 
         f.FormId = ' + CONVERT(VARCHAR(4), @FormId) + ') t
pivot
(
    max(CellValue) for ColumnName in (' + @columnsPivot + ')
) p
'
exec (@createTempSql)

-- 执行关联查询
select * 
from report r
left join #FormResults fr on r.FormId = fr.FormId

-- 清理临时表
drop table #FormResults

注意：根据CellValue的实际数据类型调整字段类型（比如用SQL_VARIANT适配多种类型，或指定具体类型）。

方案3：使用OPENROWSET调用存储过程（需配置权限） #

若SQL Server允许使用OPENROWSET，可将存储过程结果当作表关联，但需提前开启相关配置：

1. 开启Ad Hoc Distributed Queries：

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

2. 动态构造关联查询：

declare @FormId tinyint = 1
declare @sql nvarchar(max) = '
select *
from report r
left join OPENROWSET(''SQLNCLI'', ''Server=(local);Trusted_Connection=yes;'',
                     ''exec YourStoredProcedureName @FormId=' + CONVERT(VARCHAR(4), @FormId) + ''') fr
on r.FormId = fr.FormId
'
exec (@sql)

注意：需替换YourStoredProcedureName为实际存储过程名称，此方案依赖权限配置，且列结构变更时可能出现不兼容问题，适合列结构相对稳定的场景。

内容的提问来源于stack exchange，提问作者fery