SQL Dynamic Query Error ( Column name invalid for a value that isn't a column name )-CodePudding

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 " instead of '.

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 < @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 >= ' @SDstr ' AND FullDateUSA <= ' @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)

;

CodePudding user response：

I am assuming because you are setting it as a constant here -

SET @LocationNameFromApp = 'Headquarters';

dynamic query translates it as LocationName=Headquarters instead of LocationName='Headquarters'.

Try and change the setting of the variable as below -

SET @LocationNameFromApp = '''Headquarters''';

so it will be translated to LocationName='Headquarters'.