Must declare 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 a pivot table. The value in @var is the value that already exists in the dynamic table Year INT. Everything works fine except when I try to perform a dynamic rotation. This gives me an error that is @myTablenot declared, although I am running all the code at the same time. The problem is to be in the summary declaration, I really do not find the problem. Any ideas?

Answer 1

Use #temp table and sp_executesqlonly works 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

.

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

Answer 3

, .

DECLARE @DynamicPivot VARCHAR(MAX) 

DECLARE @var INT 
SET @var = 2015

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

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

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

EXEC sp_executesql @DynamicPivot