Declare @sql INT
SET @sql = 2
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @SelectColumnName AS NVARCHAR(MAX)
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(REPLACE(CONVERT(NVARCHAR,SDate, 106), ' ', '-'))
FROM (SELECT DISTINCT SDate FROM tblDuty WHERE SDate BETWEEN GETDATE() AND DATEADD(d, 7, GETDATE())) AS Week
SELECT @SelectColumnName
= ISNULL(@SelectColumnName + ',','')
+ 'ISNULL(' + QUOTENAME(REPLACE(CONVERT(NVARCHAR,SDate, 106), ' ', '-')) + ', 0) AS '
+ QUOTENAME(REPLACE(CONVERT(NVARCHAR,SDate, 106), ' ', '-'))
FROM (SELECT DISTINCT SDate FROM tblDuty WHERE SDate BETWEEN GETDATE() AND DATEADD(d, 7, GETDATE())) AS Week
SET @DynamicPivotQuery =
N'SELECT StIdNo, dbo.InitCap(EmpName) As EmpName, dbo.InitCap(Job) AS Job, ' + @SelectColumnName +
'FROM
(
SELECT REPLACE(CONVERT(CHAR(15), SDate, 106),'' '',''-'') AS SDate, StIdNo, EmpName, Job, ShiftId FROM tblDuty INNER JOIN
tblSessions ON tblDuty.SessonId = tblSessions.SSId INNER JOIN
tblEmpENg ON tblEmpEng.EmpNo = tblDuty.StIdNo INNER JOIN
tblDutyStaffs ON tblDutyStaffs.StaffId = tblDuty.StIdNo INNER JOIN
tblDutyGroup ON tblDutyGroup.GrNo = tblDutyStaffs.ShiftGrId
WHERE tblDutyGroup.GrNo = @Sql
GROUP BY StIdNo, SDate, ShiftId, SessonId, EmpName, Job
) x
PIVOT
(
SUM(x.ShiftId) for x.SDate in (' + @ColumnName + ')
) P ORDER BY EmpName ASC'
EXEC(@DynamicPivotQuery);
When I run the query I got the following error
Msg 137, Level 15, State 2, Line 8Must declare the scalar variable "@Sql".
