How to declare variables within dynamic columns in SQL Server 2005 using PIVOT

Question

I've checked out this and this, but I am lost.

For some reason I cannot declare the @wsDateFrom & @wsDateTo variables to be used inside the following dynamic column SQL code.

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX)

DECLARE @wsDateFrom AS smalldatetime
DECLARE @wsDateTo   AS smalldatetime

SET @wsDateFrom = '01-JAN-2015'
SET @wsDateTo   = '30-JUN-2015'

select @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(a.AbsenceDescription) 
                      FROM dbo.tblAbsentCodes AS a
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

set @query = N'SELECT StudentID, ' + @cols + 
             'FROM  (SELECT a.StudentID, ab.AbsenceDescription, a.AttendanceID
                     FROM   dbo.tblAttendance AS a
                        INNER JOIN tblCalendar c 
                            ON a.DateID = c.DateID
                        INNER JOIN tblAbsentCodes as ab
                            ON ab.AbsenceID = a.AbsenceID
                     WHERE c.DayDate BETWEEN @wsDateFrom AND @wsDateTo) AS p
              PIVOT (COUNT(AttendanceID) FOR AbsenceDescription IN (' + @cols + ')) AS pvt '

execute(@query)

The error I am getting is

Must declare the scalar variable "@wsDateFrom".

But it's there! Or should I be placing the DECLARE somehow inside the @query?

If that's the case then how would I pass those two date variables in a function or stored-procedure? It would open it up for sql-injection wouldn't it?

Answer 1

No, it's NOT there. Try this:

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX)

DECLARE @wsDateFrom AS smalldatetime
DECLARE @wsDateTo   AS smalldatetime

SET @wsDateFrom = '01-JAN-2015'
SET @wsDateTo   = '30-JUN-2015'

select @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(a.AbsenceDescription) 
                      FROM dbo.tblAbsentCodes AS a
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

set @query = N'SELECT StudentID, ' + @cols + 
             'FROM  (SELECT a.StudentID, ab.AbsenceDescription, a.AttendanceID
                     FROM   dbo.tblAttendance AS a
                        INNER JOIN tblCalendar c 
                            ON a.DateID = c.DateID
                        INNER JOIN tblAbsentCodes as ab
                            ON ab.AbsenceID = a.AbsenceID
                     WHERE c.DayDate BETWEEN ' + @wsDateFrom + 'AND ' + @wsDateTo + ' ) AS p
              PIVOT (COUNT(AttendanceID) FOR AbsenceDescription IN (' + @cols + ')) AS pvt '

execute(@query)