How to insert into a table variable with a dynamic query?

Question

I tried to develop this stored procedure using a temp table but that wouldn't work so I switched to using a table variable. I need to execute an interim dynamic query into the table variable and then I use that table variable to execute the final query. The problem is that I receive an error "must declare scalar variable @clms". I assume that Exec doesn't have scope for the table variable?

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)

Solution 1

This is simple minimal example. You can use INSERT EXEC statement. The key is to have table variable declared inside and outside dynamic query. At the end of dynamic query just select from table variable and insert resultset into outside table variable:

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

Solution 2

I found this attempting to do basically the same thing. I altered my SQL, and yes, it works! But then I thought, this is overcomplicating things. Why declare the table variable, insert, then select all in the dynamic SQL? Why not just select...

DECLARE @t TABLE ( id INT ) 

DECLARE @q NVARCHAR(MAX) = 'select 1 union select 2'

INSERT INTO @t
EXEC(@q)

SELECT * FROM @t

Comments

[ughai]

please do not use string concatenation to add parameters to your dynamic query. use sp_executesql with parameters

[D Stanley]

Your assumption is correct. Why wouldn't a temp table work?

[D Stanley]

Where does @UnitIDs come from? Could you put those values into another table variable? Then you wouldn't need dynamic SQL for the first query.

[Nathan Niesen]

INSERT-EXEC is no longer allowed, it errors with "An INSERT EXEC statement cannot be nested"

[Giorgi Nakeuri]

@NateN, that error means completely different thing. Please check before downvoting. Insert exec is perfectly fine itself and allowed.