如何通过动态查询插入表变量？

Question

我试图使用临时表开发此存储过程，但是那行不通，所以我改用表变量。我需要对表变量执行临时动态查询，然后使用该表变量执行最终查询。问题是我收到错误消息“必须声明标量变量@clms”。我假设Exec没有该表变量的作用域？

DECLARE @qry nvarchar(4000)
DECLARE @clms TABLE (mastcatname nvarchar(50),engdtlbeta decimal (18,4))
SET @qry='INSERT INTO @clms 
          SELECT distinct replace(mastcatname, '' '', '''') as mastcatname, 
                 engdtlbeta 
          FROM vw_Scorecard 
          WHERE empsurveyid=' + cAST(@EmpSurveyID AS nvarchar(10)) + ' 
               AND UnitID IN (' + @UnitIDs + ') 
          ORDER BY engdtlbeta desc, MastCatName'

EXEC(@qry)

DECLARE @cols nvarchar(1000)
SELECT @cols=COALESCE (@cols + ',[' + mastcatname + ']', '[' + mastcatname + ']') 
FROM @clms


SET @qry='SELECT UnitName ,
                 ParentName, ' + @cols + ' 
          FROM (
                SELECT UnitName,
                    ParentName, 
                    ScoreAvg, 
                    replace(mastcatname, '' '','''') as mastcatname 
                FROM vw_Scorecard 
                WHERE UnitID IN (' + @UnitIDs + ') 
                    AND EmpSurveyID=' + cast(@EmpSurveyID as nvarchar(5)) + ' ) p
          PIVOT
            (SUM(ScoreAvg) FOR mastcatname in (' + @cols + ')) as pvt'

EXEC (@qry)

Answer 1

这是简单的最小示例。您可以使用INSERT EXEC语句。关键是要在动态查询的内部和外部声明表变量。在动态查询结束时，只需从表变量中选择并将结果集插入外部表变量中即可：

DECLARE @t TABLE ( id INT )

DECLARE @q NVARCHAR(MAX) = 'declare @t table(id int) 
                            insert into @t values(1),(2) 
                            select * from @t'

INSERT INTO @t
EXEC(@q)

SELECT * FROM @t