Must declare the table variable "@myTable"

Question

DECLARE @myTable TABLE( 
Name VARCHAR(50), 
Year INT, 
Hours INT ) 

INSERT INTO @myTable *.... some values*

DECLARE @var INT 
SET @var = 2015

DECLARE @DynamicPivot VARCHAR(MAX) 

SET @DynamicPivot = 'SELECT * FROM
@myTable  PIVOT( SUM(Hours) FOR Year IN (' + @var + ') ) AS PvtTable

EXEC sp_executesql @DynamicPivot

I am trying to create a dynamic table and rotate the table using pivot. The value in @var is a value that already exists in the dynamic table Year INT. Everything works fine except when I try to execute the dynamic pivot. It gives me the error that @myTable is not declared even though I am running the whole code at the same time. The problem migh be in the pivot declaration by I don't really find the issue. Any ideas?

Answer 1

Use # temp table and sp_executesql works only with nvarchar:

CREATE TABLE #myTable ( 
    Name VARCHAR(50), 
    Year INT, 
    Hours INT
) 

INSERT INTO #myTable *.... some values*

DECLARE @var INT 
SET @var = 2015

DECLARE @DynamicPivot NVARCHAR(MAX) 

SET @DynamicPivot = '
SELECT * 
FROM #myTable
PIVOT( 
SUM(Hours) FOR Year IN ([' + CAST(@var as nvarchar(10)) + '])
) AS PvtTable'

EXEC sp_executesql @DynamicPivot

DROP TABLE #myTable

Answer 2

You should use the table variable inside the dynamic query..

DECLARE @DynamicPivot VARCHAR(MAX) 

SET @DynamicPivot = '
DECLARE @myTable TABLE( 
Name VARCHAR(50), 
Year INT, 
Hours INT ) 

INSERT INTO @myTable *.... some values*

DECLARE @var INT 
SET @var = 2015

SELECT * FROM
@myTable  PIVOT( SUM(Hours) FOR Year IN (' + @var + ') ) AS PvtTable'

EXEC sp_executesql @DynamicPivot