title: SQL Dynamic Query Error ( Column name invalid for a value that isn't a column name )
body: <p>Dynamic query throwing error saying invalid column name 'Headquarters'. Not sure why it would throw that error. If I take out the where statement at the end of With block it runs fine so I think it is breaking there. Please help. I have already tried restarting everything and refreshing the cache. Don't see any errors where I am using &quot; instead of '.</p>
  <pre><code>DECLARE @StartDate DATE = '10/25/2021';
  DECLARE @EndDate DATE;
  DECLARE @DynamicQuery AS NVARCHAR(MAX);
  DECLARE @ColumnList AS NVARCHAR(MAX);
  DECLARE @LocationNameFromApp AS VARCHAR(50);
  DECLARE @SDstr as NVARCHAR(10);
  DECLARE @EDstr as NVARCHAR(10);
  
  Set @EndDate = DATEADD(DAY,13,@StartDate);
  SET @SDstr = CONVERT(VARCHAR(10), @StartDate, 120);
  SET @EDstr = CONVERT(VARCHAR(10), @EndDate, 120);
  SET @LocationNameFromApp = 'Headquarters';
  
  -- populate column list 
  WITH DateRange(DateData) AS 
  (
      SELECT @StartDate as Date
      UNION ALL
      SELECT DATEADD(DAY,1,DateData)
      FROM DateRange 
      WHERE DateData &lt; @EndDate
  )
  SELECT @ColumnList = ISNULL(@ColumnList + ',', '')
          + QUOTENAME(FORMAT(DateData, 'MM/dd/yyyy'))
  FROM DateRange
  OPTION (MAXRECURSION 300);
  
  
  set @DynamicQuery = '
  
  WITH Schedule AS (
      SELECT
          P.PositionName,
          S.ShiftName,
          U.FirstName,
          U.LastName,
          R.RotationName,
          D.FullDateUSA,
          L.LocationName
      FROM
          dbo.factSchedule F
          INNER JOIN dbo.Users U on F.UserID = U.UserID
          INNER JOIN dbo.DimDate D on F.KeyDate = D.DateKey
          INNER JOIN dbo.Positions P on F.PositionID = P.PositionID
          INNER JOIN dbo.Shifts S on F.ShiftID = S.ShiftID
          INNER JOIN dbo.Rotations R on F.RotationID = R.RotationID
          INNER JOIN dbo.Locations L on F.LocationID = L.LocationID
      WHERE LocationName='+@LocationNameFromApp+' AND FullDateUSA &gt;= '+@SDstr+' AND FullDateUSA &lt;= '+@EDstr+'
  )
  
  SELECT * INTO dbo.'+@LocationNameFromApp+'SchedDays
  FROM Schedule
      PIVOT (MAX(ShiftName) FOR FullDateUSA IN ('+@ColumnList+')) P  
      
  ALTER TABLE dbo.'+@LocationNameFromApp+'SchedDays ADD ID int identity(1,1) not null
  
  ALTER TABLE dbo.'+@LocationNameFromApp+'SchedDays ADD CONSTRAINT '+@LocationNameFromApp+'pk_ID primary key(ID)
  
      '
  execute (@DynamicQuery)
  
  ;
  </code></pre>