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)
You can use a table variable with dynamic SQL, but you must declare the table inside the dynamic SQL itself.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With