我尝试使用临时表开发此存储过程,但这不起作用,因此我改用表变量。我需要对表变量执行临时动态查询,然后使用该表变量执行最终查询。问题是我收到错误“必须声明标量变量@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)
这是简单的最小示例。您可以使用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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)