SQL 如何使用动态查询插入表变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30891932/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert into a table variable with a dynamic query?
提问by JonnyBravoJr
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?
我尝试使用临时表开发此存储过程，但这不起作用，因此我转而使用表变量。我需要对表变量执行临时动态查询，然后使用该表变量执行最终查询。问题是我收到一个错误“必须声明标量变量@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)
回答by Giorgi Nakeuri
This is simple minimal example. You can use INSERT EXECstatement. 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:
这是简单的最小示例。您可以使用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
回答by John Chase
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...
我发现这试图做基本上相同的事情。我修改了我的 SQL，是的，它有效！但后来我想，这太复杂了。为什么在动态SQL中声明表变量，插入，然后全选？为什么不直接选择...
DECLARE @t TABLE ( id INT )
DECLARE @q NVARCHAR(MAX) = 'select 1 union select 2'
INSERT INTO @t
EXEC(@q)
SELECT * FROM @t
