title: declare the scalar variable in stored procedure
body: <p>I'm getting this error:
  Must declare the scalar variable "@TempTable1".
  The query works if i remove single quote from around @Temptable1 but no any rows returned.</p>
  
  <pre><code>    ALTER PROCEDURE dbo.StoredProcedure2
        @Studentcode int
      AS
        DECLARE @sql AS nvarchar(max)
        DECLARE @pivot_list AS nvarchar(max) -- Leave NULL for COALESCE technique
        DECLARE @select_list AS nvarchar(max) -- Leave NULL for COALESCE technique
        Declare @TempTable1 AS table(CourseTitel nvarchar(50) , Grade nvarchar(50))
  
        INSERT INTO @TempTable1 (CourseTitel, Grade) 
         SELECT Courses.CourseTitel, Status.Status 
         FROM Status 
           INNER JOIN Courses ON Status.CourseCode = Courses.CourseCode 
         WHERE (Status.StudentCode = @Studentcode)
  
        SELECT @pivot_list = COALESCE(@pivot_list + ', ','') + '[' + CONVERT(varchar, PIVOT_CODE) + ']',
               @select_list = COALESCE(@select_list + ', ','') + '[' + CONVERT(varchar, PIVOT_CODE) + '] AS [col_' + CONVERT(varchar, PIVOT_CODE) + ']'
        FROM (SELECT DISTINCT PIVOT_CODE 
              FROM (SELECT CourseTitel, Grade, ROW_NUMBER() OVER (PARTITION BY CourseTitel ORDER BY Grade) AS PIVOT_CODE 
                    FROM @TempTable1) 
              AS rows) 
        AS  PIVOT_CODES
  
      SET @sql = ';WITH p AS (SELECT CourseTitel, Grade, 
                  ROW_NUMBER() OVER (PARTITION BY urseTitel ORDER BY Grade) AS PIVOT_CODE 
                  FROM ' + @TempTable1 + ' ) SELECT CourseTitel, ' + select_list + '
                  FROM p PIVOT (MIN(Grade)FOR PIVOT_CODE IN (' + @pivot_list + ')) AS pvt'
      --PRINT (@sql)
      EXEC (@sql)
      /* SET NOCOUNT ON */    
  </code></pre>