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)

please do not use string concatenation to add parameters to your dynamic query. use sp_executesql with parameters — ughai
– ughai, Commented Jun 17, 2015 at 12:43
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. — D Stanley
– D Stanley, Commented Jun 17, 2015 at 12:46

Giorgi Nakeuri · Accepted Answer · 2015-06-17 12:45:46Z

31

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

answered Jun 17, 2015 at 12:45

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nathan Niesen Over a year ago

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

Giorgi Nakeuri Over a year ago

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

codeulike Over a year ago

In SQL Server 2019 you dont need to declare the table variable inside the dynamic sql

John Chase · Accepted Answer · 2019-05-29 10:27:15Z

4

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

answered May 29, 2019 at 10:27

John Chase

3801 gold badge3 silver badges13 bronze badges

1 Comment

codeulike Over a year ago

can confirm this works in Sql Server 2019

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related